Simple example on why LIMIT is not always the best thing for SQL Database

Since our production database grew to the size where querying data without indices became a suicide mission, we have learned that LIMIT does not always translate into faster query time and less work on the machine.

Today at work, we ran into a beautifully simple example to illustrate this point, which was perceived as counter-intuitive by more junior teammates.

Here we have a query over a 8M-row table, the condition fits squarely into an index.

EXPLAIN SELECT "record".*
FROM "record" WHERE ("record"."status" = 'not_found' AND "record"."updated_date" < '2018-12-23 15:48:23.008320+00:00');

QUERY PLAN

Bitmap Heap Scan on record  (cost=256890.59..3475874.26 rows=8448978 width=1740)
Recheck Cond: (((status)::text = 'not_found'::text) AND (updated_date < '2018-12-23 15:48:23.00832+00'::timestamp with time zone))
->  Bitmap Index Scan on not_found_records_idx  (cost=0.00..254778.35 rows=8448978 width=0)
Index Cond: (((status)::text = 'not_found'::text) AND (updated_date < '2018-12-23 15:48:23.00832+00'::timestamp with time zone))
(4 rows)

That was still lot of records, so we were hoping a LIMIT clause would shorten the execution time.

EXPLAIN SELECT "record".*
FROM "record" WHERE ("record"."status" = 'not_found' AND "record"."updated_date" < '2018-12-23 15:48:23.008320+00:00') LIMIT 1;

QUERY PLAN

Limit  (cost=0.00..0.46 rows=1 width=1740)
   ->  Seq Scan on record  (cost=0.00..3923250.74 rows=8448978 width=1740)
         Filter: ((updated_date < '2018-12-23 15:48:23.00832+00'::timestamp with time zone) AND ((status)::text = 'not_found'::text))
(3 rows)

Only that it didn’t. Adding the LIMIT clause changed the query plan from Bitmap Index Scan to a Sequence Scan, which is about the worst thing we want to do on a 8M-row table.

It is a typical “optimization” of the planner, who thought that the query with LIMIT clause was too small.

Operation wise, an index scan is more expensive than a sequence scan. An index scan would require to read the index pages first and then read the data pages for relevant rows.

The sequence scan only deal with data pages. And so when the LIMIT is small, combined with the table statistics, the planner is tempted to believe there are enough (random) rows that match the filter condition, which makes a sequence scan cheaper.

In fact, somehow this table statistics suggests that every LIMIT is too small till it is half of the table!

EXPLAIN SELECT "record".*
FROM "record" WHERE ("record"."status" = 'not_found' AND "record"."updated_date" < '2018-12-23 15:48:23.008320+00:00') LIMIT 3000000;

QUERY PLAN

Limit  (cost=0.00..1393038.57 rows=3000000 width=1740)
   ->  Seq Scan on record  (cost=0.00..3923250.74 rows=8448978 width=1740)
         Filter: ((updated_date < '2018-12-23 15:48:23.00832+00'::timestamp with time zone) AND ((status)::text = 'not_found'::text))
(3 rows)

EXPLAIN SELECT "record".*
FROM "record" WHERE ("record"."status" = 'not_found' AND "record"."updated_date" < '2018-12-23 15:48:23.008320+00:00') LIMIT 4000000;

Adding an ORDER BY clause into the query brings structure to the search and guide the planner to use the index

EXPLAIN SELECT "record".*
FROM "record" WHERE ("record"."status" = 'not_found' AND "record"."updated_date" < '2018-12-23 15:48:23.008320+00:00') ORDER BY "record"."imported_date" LIMIT 1;

QUERY PLAN

Limit  (cost=3518127.15..3518127.15 rows=1 width=1740)
   ->  Sort  (cost=3518127.15..3539249.59 rows=8448978 width=1740)
         Sort Key: imported_date
         ->  Bitmap Heap Scan on record  (cost=256898.59..3475882.26 rows=8448978 width=1740)
               Recheck Cond: (((status)::text = 'not_found'::text) AND (updated_date < '2018-12-23 15:48:23.00832+00'::timestamp with time zone))
               ->  Bitmap Index Scan on not_found_records_idx  (cost=0.00..254786.35 rows=8448978 width=0)
                     Index Cond: (((status)::text = 'not_found'::text) AND (updated_date < '2018-12-23 15:48:23.00832+00'::timestamp with time zone))
(7 rows)

Actually, any random ORDER BY would do

EXPLAIN SELECT "record".*
FROM "record" WHERE ("record"."status" = 'not_found' AND "record"."updated_date" < '2018-12-23 15:48:23.008320+00:00') ORDER BY random() LIMIT 1;

QUERY PLAN

Limit  (cost=3539253.59..3539253.60 rows=1 width=1748)
   ->  Sort  (cost=3539253.59..3560376.04 rows=8448978 width=1748)
         Sort Key: (random())
         ->  Bitmap Heap Scan on record  (cost=256902.59..3497008.70 rows=8448978 width=1748)
               Recheck Cond: (((status)::text = 'not_found'::text) AND (updated_date < '2018-12-23 15:48:23.00832+00'::timestamp with time zone))
               ->  Bitmap Index Scan on not_found_records_idx  (cost=0.00..254790.35 rows=8448978 width=0)
                     Index Cond: (((status)::text = 'not_found'::text) AND (updated_date < '2018-12-23 15:48:23.00832+00'::timestamp with time zone))
(7 rows)

However, that is still far from optimal. In order to return the first row, the database first needs to fetch all rows matching the index condition, and only after that, sorts. This creates strains on the machine memory and maybe even disk swap.

Index is actually ordered structure (BTree). The most optimal query is the one using one of the indexed columns for sorting.

EXPLAIN SELECT "record".*
FROM "record" WHERE ("record"."status" = 'not_found' AND "record"."updated_date" < '2018-12-23 15:48:23.008320+00:00') ORDER BY "record"."updated_date" LIMIT 1;

QUERY PLAN

 Limit  (cost=0.56..1.90 rows=1 width=1740)
   ->  Index Scan using not_found_records_idx on record  (cost=0.56..11280384.47 rows=8448978 width=1740)
         Index Cond: (((status)::text = 'not_found'::text) AND (updated_date < '2018-12-23 15:48:23.00832+00'::timestamp with time zone))
(3 rows)

This, in fact, is even more effective that the original no-order query, with or without LIMIT.

458 thoughts on “Simple example on why LIMIT is not always the best thing for SQL Database

  1. Pingback: ivermectin for aml
  2. Pingback: ivermectol 12 mg
  3. Pingback: best price viagra
  4. Pingback: viagra 50mg cost
  5. Pingback: all about viagra
  6. Pingback: cialis capsule
  7. Pingback: does viagra expire
  8. Pingback: viagra cost at cvs
  9. Pingback: albuterol 5 mg
  10. Pingback: ivermectin 4 mg
  11. Pingback: cialis walmart
  12. Pingback: ivermectin
  13. Pingback: flcc blackboard
  14. Pingback: Anonymous
  15. Pingback: imask protocol
  16. Pingback: ivermectin 15 mg
  17. Pingback: Anonymous
  18. Pingback: ivermectin 50ml
  19. Pingback: ivermectin buy
  20. Pingback: stromectol buy uk
  21. Pingback: Anonymous
  22. Pingback: cost of ivermectin
  23. Pingback: generic cialis
  24. Pingback: cheap cialis
  25. Pingback: buy provigil usa
  26. Pingback: ivermectin 200
  27. Pingback: tadalafil otc
  28. Pingback: cialis peak effect
  29. Pingback: ivermectin wiki
  30. Pingback: buy cialis jelly
  31. Pingback: cialis on line
  32. Pingback: sildenafil pills
  33. Pingback: tadalafil mexico
  34. Pingback: cialis everyday
  35. Pingback: prescription drugs
  36. Pingback: ivermectin uk
  37. Pingback: cialis coupon
  38. Pingback: tadalafil pills
  39. Pingback: 3disparage
  40. Pingback: generic cialis usa
  41. Pingback: ivermectin 4000
  42. Pingback: viagra wirkung
  43. Pingback: ivermectin mexico
  44. Pingback: keflex alcohol
  45. Pingback: cialis best price
  46. Pingback: generic cialis
  47. Pingback: cephalexin use
  48. Pingback: ivermectin gold
  49. Pingback: clomid tablets
  50. Pingback: chloramphenicol
  51. Pingback: mectin ivermectin
  52. Pingback: flagyl medication
  53. Pingback: cephalexin order
  54. Pingback: 500mg azithromycin
  55. Pingback: augmentin 500mg
  56. Pingback: ivermectin 3mg
  57. Pingback: cheap ivermectin
  58. Pingback: generic stromectol
  59. Pingback: lasixs water pill
  60. Pingback: ivermectin 2%
  61. Pingback: viagra soft pills
  62. Pingback: ivermectin 3mg otc
  63. Pingback: low cost cialis
  64. Pingback: buy cialis viagra
  65. Pingback: 150 mg clomid
  66. Pingback: clomid pill online
  67. Pingback: Website
  68. Pingback: cause of ed
  69. Pingback: erection pills
  70. Pingback: ivermectin hiv
  71. Pingback: mazhor4sezon
  72. Pingback: filmfilmfilmes
  73. Pingback: ivermectin mexico
  74. Pingback: gRh9UPV
  75. Pingback: 9-05-2022
  76. Pingback: kinoteatrzarya.ru
  77. Pingback: TopGun2022
  78. Pingback: Xvideos
  79. Pingback: XVIDEOSCOM Videos
  80. Pingback: ivanesva
  81. Pingback: hydroxychloroquine
  82. Pingback: cialis coupon
  83. Pingback: cost of viagra
  84. Pingback: Netflix
  85. Pingback: FILM
  86. Pingback: designchita.ru
  87. Pingback: YA-krasneyu
  88. Pingback: design-human.ru
  89. Pingback: designmsu.ru
  90. Pingback: vkl-design.ru
  91. Pingback: irida-design.ru
  92. Pingback: cheap stromectol
  93. Pingback: ivermectin malaria
  94. Pingback: projectio
  95. Pingback: psy online
  96. Pingback: Gz92uNNH
  97. Pingback: do-posle-psihologa
  98. Pingback: uels ukrain
  99. Pingback: DPTPtNqS
  100. Pingback: qQ8KZZE6
  101. Pingback: D6tuzANh
  102. Pingback: SHKALA TONOV
  103. Pingback: chelovek-iz-90-h
  104. Pingback: 3Hk12Bl
  105. Pingback: 3NOZC44
  106. Pingback: 01211
  107. Pingback: tor-lyubov-i-grom
  108. Pingback: film-tor-2022
  109. Pingback: hd-tor-2022
  110. Pingback: hdorg2.ru
  111. Pingback: JXNhGmmt
  112. Pingback: Psikholog
  113. Pingback: netstate.ru
  114. Pingback: Link
  115. Pingback: nih ivermectin
  116. Pingback: psy
  117. Pingback: viagra
  118. Pingback: revatio
  119. Pingback: A片
  120. Pingback: meriking
  121. Pingback: buy viagra no rx
  122. Pingback: madridbet
  123. Pingback: viagra canada
  124. Pingback: ivermectine
  125. Pingback: stromectol uk
  126. Pingback: canadian drug
  127. Pingback: stromectol uk
  128. Pingback: stromectol stock
  129. Pingback: generic stromectol
  130. Pingback: stromectol rosacea
  131. Pingback: stromectol pill
  132. Pingback: canadian rx
  133. Pingback: canadadrugs
  134. Pingback: madridbet
  135. Pingback: pharmacy
  136. Pingback: canadian drugstore
  137. Pingback: meritking
  138. Pingback: stromectol oral
  139. Pingback: stromectol india
  140. Pingback: ivermectine
  141. Pingback: canadadrugs
  142. Pingback: grandpashabet
  143. Pingback: canadian pharmacys
  144. Pingback: canada drugs
  145. Pingback: buy viagra usa
  146. Pingback: ed drugs generic
  147. Pingback: cheapest ed drugs
  148. Pingback: buy viagra 25mg

Leave a Reply