API tools faq
paste
Advertisement
henk53

Adding limit 1 - query goes from few ms to several hours‏‏

henk53
Oct 14th, 2012
239
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 16.62 KB | None | 0 0
raw download clone embed print report
  1. On PG 9.1 and 9.2 I'm running the following query:
  2.  
  3. SELECT
  4. *
  5. FROM
  6. stream_store
  7. JOIN
  8. (
  9. SELECT
  10. UNNEST(stream_store_ids) AS id
  11. FROM
  12. stream_store_version_index
  13. WHERE
  14. stream_id = 607106 AND
  15. version = 11
  16. ) AS records USING (id)
  17. ORDER BY
  18. id DESC
  19.  
  20. This takes several (10 to 20) milliseconds at most.
  21.  
  22. When I add a LIMIT 1 to the end of the query, the query time goes to several hours(!).
  23.  
  24. The full version String of PG 9.1 is "PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit". The 9.1 machine is a socket 771 dual quad core at 3.16Ghz with 64GB memory and 10 Intel x25M SSDs in a RAID5 setup on 2 ARECA 1680 RAID controllers. The "stream_store" table has 122 million rows and is partitioned. The array that's being unnested for the join has 27 entries.
  25.  
  26. Without the LIMIT 1, the plan is as follows:
  27.  
  28. "Sort (cost=247270.56..248786.41 rows=606341 width=1191) (actual time=0.476..0.479 rows=27 loops=1)"
  29. " Sort Key: public.stream_store.id"
  30. " Sort Method: quicksort Memory: 38kB"
  31. " -> Nested Loop (cost=0.00..23.80 rows=606341 width=1191) (actual time=0.070..0.409 rows=27 loops=1)"
  32. " Join Filter: (public.stream_store.id = (unnest(stream_store_version_index.stream_store_ids)))"
  33. " -> Index Scan using stream_store_version_index_unique_idx on stream_store_version_index (cost=0.00..2.67 rows=1 width=383) (actual time=0.027..0.035 rows=27 loops=1)"
  34. " Index Cond: ((stream_id = 607106) AND (version = 11))"
  35. " -> Append (cost=0.00..21.03 rows=7 width=1296) (actual time=0.008..0.012 rows=1 loops=27)"
  36. " -> Seq Scan on stream_store (cost=0.00..0.00 rows=1 width=1385) (actual time=0.000..0.000 rows=0 loops=27)"
  37. " -> Index Scan using stream_store_slice0_id on stream_store_slice0 stream_store (cost=0.00..2.67 rows=1 width=1385) (actual time=0.001..0.001 rows=0 loops=27)"
  38. " Index Cond: (id = (unnest(stream_store_version_index.stream_store_ids)))"
  39. " -> Index Scan using stream_store_slice1_id on stream_store_slice1 stream_store (cost=0.00..2.67 rows=1 width=1385) (actual time=0.000..0.000 rows=0 loops=27)"
  40. " Index Cond: (id = (unnest(stream_store_version_index.stream_store_ids)))"
  41. " -> Index Scan using stream_store_slice2_id on stream_store_slice2 stream_store (cost=0.00..4.93 rows=1 width=1179) (actual time=0.002..0.002 rows=0 loops=27)"
  42. " Index Cond: (id = (unnest(stream_store_version_index.stream_store_ids)))"
  43. " -> Index Scan using stream_store_slice3_id on stream_store_slice3 stream_store (cost=0.00..3.95 rows=1 width=1244) (actual time=0.003..0.004 rows=1 loops=27)"
  44. " Index Cond: (id = (unnest(stream_store_version_index.stream_store_ids)))"
  45. " -> Index Scan using stream_store_slice4_id on stream_store_slice4 stream_store (cost=0.00..4.15 rows=1 width=1110) (actual time=0.003..0.003 rows=0 loops=27)"
  46. " Index Cond: (id = (unnest(stream_store_version_index.stream_store_ids)))"
  47. " -> Index Scan using stream_store_slice5_id on stream_store_slice5 stream_store (cost=0.00..2.67 rows=1 width=1385) (actual time=0.000..0.000 rows=0 loops=27)"
  48. " Index Cond: (id = (unnest(stream_store_version_index.stream_store_ids)))"
  49.  
  50. With the LIMIT the plan is (explained without analyze):
  51.  
  52. "Limit (cost=0.11..108.42 rows=1 width=1191)"
  53. " -> Nested Loop (cost=0.11..65673570.30 rows=606341 width=1191)"
  54. " Join Filter: (public.stream_store.id = (unnest(stream_store_version_index.stream_store_ids)))"
  55. " -> Merge Append (cost=0.11..63854546.10 rows=121268101 width=1192)"
  56. " Sort Key: public.stream_store.id"
  57. " -> Sort (cost=0.01..0.02 rows=1 width=1385)"
  58. " Sort Key: public.stream_store.id"
  59. " -> Seq Scan on stream_store (cost=0.00..0.00 rows=1 width=1385)"
  60. " -> Index Scan Backward using stream_store_slice0_id on stream_store_slice0 stream_store (cost=0.00..45.40 rows=50 width=1385)"
  61. " -> Index Scan Backward using stream_store_slice1_id on stream_store_slice1 stream_store (cost=0.00..45.40 rows=50 width=1385)"
  62. " -> Index Scan Backward using stream_store_slice2_id on stream_store_slice2 stream_store (cost=0.00..34256230.75 rows=68710693 width=1179)"
  63. " -> Index Scan Backward using stream_store_slice3_id on stream_store_slice3 stream_store (cost=0.00..21294584.57 rows=39126729 width=1244)"
  64. " -> Index Scan Backward using stream_store_slice4_id on stream_store_slice4 stream_store (cost=0.00..4595998.21 rows=13430528 width=1110)"
  65. " -> Index Scan Backward using stream_store_slice5_id on stream_store_slice5 stream_store (cost=0.00..45.40 rows=50 width=1385)"
  66. " -> Materialize (cost=0.00..2.69 rows=1 width=8)"
  67. " -> Index Scan using stream_store_version_index_unique_idx on stream_store_version_index (cost=0.00..2.67 rows=1 width=383)"
  68. " Index Cond: ((stream_id = 607106) AND (version = 11))"
  69.  
  70. It looks like that with the LIMIT 1, Postgres tries to sort the 122 million records table first, which obviously is not going to be very fast.
  71.  
  72. If I add an extra (seemingly redundant) WHERE clause to the query, then on 9.1 the query is fast again, but unfortunately not on 9.2. With the WHERE and LIMIT clauses the query is:
  73.  
  74. SELECT
  75. *
  76. FROM
  77. stream_store
  78. JOIN
  79. (
  80. SELECT
  81. UNNEST(stream_store_ids) AS id
  82. FROM
  83. stream_store_version_index
  84. WHERE
  85. stream_id = 607106 AND
  86. version = 11
  87. ) AS records USING (id)
  88. WHERE
  89. stream_store.stream_id = 607106
  90. ORDER BY
  91. id DESC
  92. LIMIT 1
  93.  
  94. Postgres 9.1 comes up with the following plan for the above query, which is pretty fast:
  95.  
  96. "Limit (cost=25.57..25.58 rows=1 width=1189) (actual time=27.116..27.117 rows=1 loops=1)"
  97. " -> Sort (cost=25.57..26.45 rows=349 width=1189) (actual time=27.115..27.115 rows=1 loops=1)"
  98. " Sort Key: public.stream_store.id"
  99. " Sort Method: top-N heapsort Memory: 25kB"
  100. " -> Nested Loop (cost=0.00..23.83 rows=349 width=1189) (actual time=26.639..27.062 rows=27 loops=1)"
  101. " Join Filter: (public.stream_store.id = (unnest(stream_store_version_index.stream_store_ids)))"
  102. " -> Index Scan using stream_store_version_index_unique_idx on stream_store_version_index (cost=0.00..2.67 rows=1 width=383) (actual time=0.363..0.373 rows=27 loops=1)"
  103. " Index Cond: ((stream_id = 607106) AND (version = 11))"
  104. " -> Append (cost=0.00..21.06 rows=7 width=1296) (actual time=0.981..0.986 rows=1 loops=27)"
  105. " -> Seq Scan on stream_store (cost=0.00..0.00 rows=1 width=1385) (actual time=0.000..0.000 rows=0 loops=27)"
  106. " Filter: (stream_id = 607106)"
  107. " -> Index Scan using stream_store_slice0_stream_id on stream_store_slice0 stream_store (cost=0.00..2.67 rows=1 width=1385) (actual time=0.001..0.001 rows=0 loops=27)"
  108. " Index Cond: (stream_id = 607106)"
  109. " -> Index Scan using stream_store_slice1_stream_id on stream_store_slice1 stream_store (cost=0.00..2.67 rows=1 width=1385) (actual time=0.001..0.001 rows=0 loops=27)"
  110. " Index Cond: (stream_id = 607106)"
  111. " -> Index Scan using stream_store_slice2_id on stream_store_slice2 stream_store (cost=0.00..4.93 rows=1 width=1179) (actual time=0.002..0.002 rows=0 loops=27)"
  112. " Index Cond: (id = (unnest(stream_store_version_index.stream_store_ids)))"
  113. " Filter: (stream_id = 607106)"
  114. " -> Index Scan using stream_store_slice3_id on stream_store_slice3 stream_store (cost=0.00..3.95 rows=1 width=1244) (actual time=0.976..0.977 rows=1 loops=27)"
  115. " Index Cond: (id = (unnest(stream_store_version_index.stream_store_ids)))"
  116. " Filter: (stream_id = 607106)"
  117. " -> Index Scan using stream_store_slice4_id on stream_store_slice4 stream_store (cost=0.00..4.16 rows=1 width=1110) (actual time=0.003..0.003 rows=0 loops=27)"
  118. " Index Cond: (id = (unnest(stream_store_version_index.stream_store_ids)))"
  119. " Filter: (stream_id = 607106)"
  120. " -> Index Scan using stream_store_slice5_stream_id on stream_store_slice5 stream_store (cost=0.00..2.67 rows=1 width=1385) (actual time=0.001..0.001 rows=0 loops=27)"
  121. " Index Cond: (stream_id = 607106)"
  122.  
  123.  
  124. Postgres 9.2 is different though, but this is also a different machine and it has a different snapshot of the database data. The full version string of this 9.2 machine is "PostgreSQL 9.2.0 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.1-7) 4.7.1, 64-bit". It has a single E5-1620 3.6-3.8GHz CPU, 32GB of memory, and an LSI 9625 with 8 Samsung 830 SSDs in RAID 6. The "stream_store" table has 114 million rows and is again partitioned. The array that's being unnested for the join still has 27 entries.
  125.  
  126. In this case, for the query with the extra WHERE clause and the LIMIT clause the following plan is created:
  127.  
  128. "Limit (cost=0.11..3148.54 rows=1 width=1197)"
  129. " -> Nested Loop (cost=0.11..102720878.16 rows=32626 width=1197)"
  130. " Join Filter: (public.stream_store.id = (unnest(stream_store_version_index.stream_store_ids)))"
  131. " -> Merge Append (cost=0.11..102622993.83 rows=65253 width=1197)"
  132. " Sort Key: public.stream_store.id"
  133. " -> Sort (cost=0.01..0.02 rows=1 width=1385)"
  134. " Sort Key: public.stream_store.id"
  135. " -> Seq Scan on stream_store (cost=0.00..0.00 rows=1 width=1385)"
  136. " Filter: (stream_id = 607106)"
  137. " -> Index Scan Backward using stream_store_slice0_id on stream_store_slice0 stream_store (cost=0.00..52.93 rows=1 width=1385)"
  138. " Filter: (stream_id = 607106)"
  139. " -> Index Scan Backward using stream_store_slice1_id on stream_store_slice1 stream_store (cost=0.00..52.93 rows=1 width=1385)"
  140. " Filter: (stream_id = 607106)"
  141. " -> Index Scan Backward using stream_store_slice2_id on stream_store_slice2 stream_store (cost=0.00..63486684.41 rows=40081 width=1187)"
  142. " Filter: (stream_id = 607106)"
  143. " -> Index Scan Backward using stream_store_slice3_id on stream_store_slice3 stream_store (cost=0.00..36776078.12 rows=20576 width=1250)"
  144. " Filter: (stream_id = 607106)"
  145. " -> Index Scan Backward using stream_store_slice4_id on stream_store_slice4 stream_store (cost=0.00..2358077.39 rows=4592 width=1045)"
  146. " Filter: (stream_id = 607106)"
  147. " -> Index Scan Backward using stream_store_slice5_id on stream_store_slice5 stream_store (cost=0.00..52.93 rows=1 width=1385)"
  148. " Filter: (stream_id = 607106)"
  149. " -> Materialize (cost=0.00..5.08 rows=100 width=8)"
  150. " -> Index Scan using stream_store_version_index_unique_idx on stream_store_version_index (cost=0.00..3.58 rows=100 width=386)"
  151. " Index Cond: ((stream_id = 607106) AND (version = 11))"
  152.  
  153. This is the version again where PG seems to sort the entire stream_store table first, which is incredibly slow.
  154.  
  155.  
  156. Out of pure desperation, I also tried the following really redundant query on 9.2, and this is one fast again:
  157.  
  158. SELECT
  159. *
  160. FROM
  161. stream_store
  162. JOIN
  163. (
  164. SELECT
  165. UNNEST(stream_store_ids) AS id
  166. FROM
  167. stream_store_version_index
  168. WHERE
  169. stream_id = 607106 AND
  170. version = 11
  171. ) AS records USING (id)
  172. WHERE
  173. stream_store.stream_id = 607106 AND
  174. stream_store.id IN (
  175. SELECT
  176. UNNEST(stream_store_ids)
  177. FROM
  178. stream_store_version_index
  179. WHERE
  180. stream_id = 607106 AND
  181. version = 27
  182. ORDER BY
  183. id DESC
  184. )
  185. ORDER BY
  186. id DESC
  187. LIMIT 1
  188.  
  189. In this case PG 9.2 comes up with the following plan:
  190.  
  191. "Limit (cost=20.62..20.99 rows=1 width=1197) (actual time=0.053..0.053 rows=0 loops=1)"
  192. " -> Nested Loop (cost=20.62..5978.93 rows=16313 width=1197) (actual time=0.053..0.053 rows=0 loops=1)"
  193. " -> Merge Join (cost=20.62..22.62 rows=100 width=16) (actual time=0.052..0.052 rows=0 loops=1)"
  194. " Merge Cond: ((unnest(public.stream_store_version_index.stream_store_ids)) = (unnest(public.stream_store_version_index.stream_store_ids)))"
  195. " -> Sort (cost=7.90..8.15 rows=100 width=8) (actual time=0.039..0.039 rows=1 loops=1)"
  196. " Sort Key: (unnest(public.stream_store_version_index.stream_store_ids))"
  197. " Sort Method: quicksort Memory: 26kB"
  198. " -> Index Scan using stream_store_version_index_unique_idx on stream_store_version_index (cost=0.00..3.58 rows=100 width=386) (actual time=0.021..0.023 rows=27 loops=1)"
  199. " Index Cond: ((stream_id = 607106) AND (version = 11))"
  200. " -> Sort (cost=12.72..12.97 rows=100 width=8) (actual time=0.012..0.012 rows=0 loops=1)"
  201. " Sort Key: (unnest(public.stream_store_version_index.stream_store_ids))"
  202. " Sort Method: quicksort Memory: 25kB"
  203. " -> HashAggregate (cost=8.40..9.40 rows=100 width=8) (actual time=0.006..0.006 rows=0 loops=1)"
  204. " -> Sort (cost=6.90..7.15 rows=100 width=394) (actual time=0.005..0.005 rows=0 loops=1)"
  205. " Sort Key: public.stream_store_version_index.id"
  206. " Sort Method: quicksort Memory: 25kB"
  207. " -> Index Scan using stream_store_version_index_unique_idx on stream_store_version_index (cost=0.00..3.58 rows=100 width=394) (actual time=0.002..0.002 rows=0 loops=1)"
  208. " Index Cond: ((stream_id = 607106) AND (version = 27))"
  209. " -> Append (cost=0.00..59.49 rows=7 width=1289) (never executed)"
  210. " -> Seq Scan on stream_store (cost=0.00..0.00 rows=1 width=1385) (never executed)"
  211. " Filter: ((stream_id = 607106) AND ((unnest(public.stream_store_version_index.stream_store_ids)) = id))"
  212. " -> Index Scan using stream_store_slice0_id on stream_store_slice0 stream_store (cost=0.00..1.28 rows=1 width=1385) (never executed)"
  213. " Index Cond: (id = (unnest(public.stream_store_version_index.stream_store_ids)))"
  214. " Filter: (stream_id = 607106)"
  215. " -> Index Scan using stream_store_slice1_id on stream_store_slice1 stream_store (cost=0.00..1.28 rows=1 width=1385) (never executed)"
  216. " Index Cond: (id = (unnest(public.stream_store_version_index.stream_store_ids)))"
  217. " Filter: (stream_id = 607106)"
  218. " -> Index Scan using stream_store_slice2_id on stream_store_slice2 stream_store (cost=0.00..29.45 rows=1 width=1187) (never executed)"
  219. " Index Cond: (id = (unnest(public.stream_store_version_index.stream_store_ids)))"
  220. " Filter: (stream_id = 607106)"
  221. " -> Index Scan using stream_store_slice3_id on stream_store_slice3 stream_store (cost=0.00..18.02 rows=1 width=1250) (never executed)"
  222. " Index Cond: (id = (unnest(public.stream_store_version_index.stream_store_ids)))"
  223. " Filter: (stream_id = 607106)"
  224. " -> Index Scan using stream_store_slice4_id on stream_store_slice4 stream_store (cost=0.00..8.18 rows=1 width=1045) (never executed)"
  225. " Index Cond: (id = (unnest(public.stream_store_version_index.stream_store_ids)))"
  226. " Filter: (stream_id = 607106)"
  227. " -> Index Scan using stream_store_slice5_id on stream_store_slice5 stream_store (cost=0.00..1.28 rows=1 width=1385) (never executed)"
  228. " Index Cond: (id = (unnest(public.stream_store_version_index.stream_store_ids)))"
  229. " Filter: (stream_id = 607106)"
  230. "Total runtime: 0.185 ms"
  231.  
  232. I tried some variations of the above query with only the IN, or the IN and the extra WHERE, but only the version with both the JOIN and the IN is fast again.
  233.  
  234. Is there anything I can do to make the query fast again with the LIMIT on both 9.1 and 9.2 without having to specify so much duplicate constraints?
  235.  
  236. Thanks in advance
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!