explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MXvL

Settings
# exclusive inclusive rows x rows loops node
1. 0.086 88,578.531 ↓ 72.0 72 1

Unique (cost=51,829.80..51,830.13 rows=1 width=690) (actual time=88,578.431..88,578.531 rows=72 loops=1)

2.          

CTE _rsrimin

3. 5.306 108.755 ↓ 1.3 21,274 1

Append (cost=0.00..18,297.14 rows=16,719 width=435) (actual time=0.110..108.755 rows=21,274 loops=1)

4. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on ranking_search_results_items rsri_1 (cost=0.00..0.00 rows=1 width=142) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: ((search_term_id IS NULL) AND (date_of_upload = ANY ('{2019-11-05,2019-11-06}'::date[])) AND (site_id = ANY ('{342,338,163,7,335,237,77,273,186,136}'::integer[])))
5. 103.443 103.443 ↓ 1.3 21,274 1

Index Scan using ranking_search_results_items__search_term_id_date_of_uploa_idx8 on ranking_search_results_items_2019_4 rsri_2 (cost=0.43..18,297.14 rows=16,718 width=435) (actual time=0.103..103.443 rows=21,274 loops=1)

  • Index Cond: ((search_term_id IS NULL) AND (date_of_upload = ANY ('{2019-11-05,2019-11-06}'::date[])) AND (site_id = ANY ('{342,338,163,7,335,237,77,273,186,136}'::integer[])))
6.          

CTE _rsri

7. 40.263 88,417.802 ↓ 1,551.3 13,962 1

Unique (cost=33,387.62..33,387.71 rows=9 width=558) (actual time=88,302.911..88,417.802 rows=13,962 loops=1)

8. 754.139 88,377.539 ↓ 9,124.9 82,124 1

Sort (cost=33,387.62..33,387.64 rows=9 width=558) (actual time=88,302.909..88,377.539 rows=82,124 loops=1)

  • Sort Key: (COALESCE((pu.product_id)::text, ('unique_'::text || (pu.id)::text))), rsri_3.date_of_upload, (COALESCE(ppa.group_hash, ((rsri_3.url_id)::text)::character varying)), pu.updated_at DESC, rsri_3.id DESC
  • Sort Method: external merge Disk: 64984kB
9. 1,678.307 87,623.400 ↓ 9,124.9 82,124 1

Nested Loop Left Join (cost=2.82..33,387.47 rows=9 width=558) (actual time=0.611..87,623.400 rows=82,124 loops=1)

10. 84.599 1,337.969 ↓ 78,180.0 78,180 1

Nested Loop Left Join (cost=2.39..591.33 rows=1 width=525) (actual time=0.508..1,337.969 rows=78,180 loops=1)

11. 23.579 826.581 ↓ 15,807.0 15,807 1

Nested Loop Left Join (cost=1.13..588.62 rows=1 width=492) (actual time=0.312..826.581 rows=15,807 loops=1)

12. 38.890 692.353 ↓ 15,807.0 15,807 1

Nested Loop (cost=0.71..588.17 rows=1 width=483) (actual time=0.283..692.353 rows=15,807 loops=1)

  • Join Filter: ((pli.from_date <= rsri_3.date_of_upload) AND (pli.to_date >= rsri_3.date_of_upload))
  • Rows Removed by Join Filter: 10461
13. 38.666 483.271 ↓ 21,274.0 21,274 1

Nested Loop (cost=0.42..587.81 rows=1 width=459) (actual time=0.161..483.271 rows=21,274 loops=1)

14. 168.043 168.043 ↓ 21,274.0 21,274 1

CTE Scan on _rsrimin rsri_3 (cost=0.00..585.17 rows=1 width=129) (actual time=0.116..168.043 rows=21,274 loops=1)

  • Filter: ((search_term_id IS NULL) AND (search_term_id IS NULL) AND (date_of_upload = ANY ('{2019-11-05,2019-11-06}'::date[])) AND (site_id = ANY ('{342,338,163,7,335,237,77,273,186,136}'::integer[])))
15. 276.562 276.562 ↑ 1.0 1 21,274

Index Scan using product_url_pkey on product_url pu (cost=0.42..2.64 rows=1 width=330) (actual time=0.013..0.013 rows=1 loops=21,274)

  • Index Cond: (id = rsri_3.url_id)
16. 170.192 170.192 ↑ 1.0 1 21,274

Index Scan using pli_url_id on product_list_items pli (cost=0.29..0.35 rows=1 width=36) (actual time=0.006..0.008 rows=1 loops=21,274)

  • Index Cond: (product_url_id = pu.id)
  • Filter: (product_list_id = 24)
  • Rows Removed by Filter: 1
17. 110.649 110.649 ↑ 1.0 1 15,807

Index Scan using product_pkey on product p (cost=0.42..0.45 rows=1 width=13) (actual time=0.007..0.007 rows=1 loops=15,807)

  • Index Cond: (id = pu.product_id)
18. 142.263 426.789 ↑ 2.4 5 15,807

Bitmap Heap Scan on product_product_association ppa (cost=1.25..2.59 rows=12 width=49) (actual time=0.022..0.027 rows=5 loops=15,807)

  • Recheck Cond: ((pu.id = first_product_url_id) OR (pu.id = second_product_url_id) OR (pu.product_id = first_product_id) OR (pu.product_id = second_product_id))
  • Heap Blocks: exact=33036
19. 31.614 284.526 ↓ 0.0 0 15,807

BitmapOr (cost=1.25..1.25 rows=12 width=0) (actual time=0.018..0.018 rows=0 loops=15,807)

20. 63.228 63.228 ↑ 1.0 3 15,807

Bitmap Index Scan on first_product_url_id (cost=0.00..0.31 rows=3 width=0) (actual time=0.004..0.004 rows=3 loops=15,807)

  • Index Cond: (pu.id = first_product_url_id)
21. 63.228 63.228 ↑ 1.0 3 15,807

Bitmap Index Scan on second_product_url_id (cost=0.00..0.31 rows=3 width=0) (actual time=0.004..0.004 rows=3 loops=15,807)

  • Index Cond: (pu.id = second_product_url_id)
22. 63.228 63.228 ↑ 1.0 3 15,807

Bitmap Index Scan on product_product_association_first_product_id_idx (cost=0.00..0.31 rows=3 width=0) (actual time=0.004..0.004 rows=3 loops=15,807)

  • Index Cond: (pu.product_id = first_product_id)
23. 63.228 63.228 ↑ 1.0 3 15,807

Bitmap Index Scan on product_product_association_second_product_id_idx (cost=0.00..0.31 rows=3 width=0) (actual time=0.004..0.004 rows=3 loops=15,807)

  • Index Cond: (pu.product_id = second_product_id)
24. 390.900 390.900 ↓ 0.0 0 78,180

Index Scan using rsri_id on variant v (cost=0.43..5.14 rows=9 width=171) (actual time=0.004..0.005 rows=0 loops=78,180)

  • Index Cond: (rsri_id = rsri_3.id)
  • Filter: selected
  • Rows Removed by Filter: 1
25.          

SubPlan (for Nested Loop Left Join)

26. 0.000 11.264 ↓ 0.0 0 704

Limit (cost=5.43..6.54 rows=1 width=13) (actual time=0.016..0.016 rows=0 loops=704)

27.          

Initplan (for Limit)

28. 2.112 2.112 ↑ 1.0 1 704

Index Scan using product_pkey on product p2 (cost=0.42..2.64 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=704)

  • Index Cond: (id = pu.product_id)
29. 0.704 9.856 ↓ 0.0 0 704

Bitmap Heap Scan on ch_master_catalog_products cmcp (cost=2.79..5.01 rows=2 width=13) (actual time=0.014..0.014 rows=0 loops=704)

  • Recheck Cond: ((product_url_id = pu.id) OR ((web_id)::text = ($2)::text))
  • Filter: (upc IS NOT NULL)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=300
30. 3.520 9.152 ↓ 0.0 0 704

BitmapOr (cost=2.79..2.79 rows=2 width=0) (actual time=0.013..0.013 rows=0 loops=704)

31. 1.408 1.408 ↓ 0.0 0 704

Bitmap Index Scan on ch_master_catalog_products_product_url_id (cost=0.00..1.39 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=704)

  • Index Cond: (product_url_id = pu.id)
32. 4.224 4.224 ↑ 1.0 1 704

Bitmap Index Scan on ch_master_catalog_products_web_id_index (cost=0.00..1.39 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=704)

  • Index Cond: ((web_id)::text = ($2)::text)
33. 29.792 42,079.072 ↓ 0.0 0 4,256

Limit (cost=6.96..1,818.41 rows=1 width=8) (actual time=9.887..9.887 rows=0 loops=4,256)

34.          

Initplan (for Limit)

35. 0.000 0.000 ↓ 0.0 0

Limit (cost=5.43..6.54 rows=1 width=13) (never executed)

36.          

Initplan (for Limit)

37. 0.000 0.000 ↓ 0.0 0

Index Scan using product_pkey on product p2_1 (cost=0.42..2.64 rows=1 width=9) (never executed)

  • Index Cond: (id = pu.product_id)
38. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on ch_master_catalog_products cmcp_1 (cost=2.79..5.01 rows=2 width=13) (never executed)

  • Recheck Cond: ((product_url_id = pu.id) OR ((web_id)::text = ($5)::text))
  • Filter: (upc IS NOT NULL)
39. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=2.79..2.79 rows=2 width=0) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ch_master_catalog_products_product_url_id (cost=0.00..1.39 rows=1 width=0) (never executed)

  • Index Cond: (product_url_id = pu.id)
41. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ch_master_catalog_products_web_id_index (cost=0.00..1.39 rows=1 width=0) (never executed)

  • Index Cond: ((web_id)::text = ($5)::text)
42. 4.256 42,049.280 ↓ 0.0 0 4,256

Nested Loop (cost=0.42..1,811.87 rows=1 width=8) (actual time=9.880..9.880 rows=0 loops=4,256)

43. 42,045.024 42,045.024 ↓ 0.0 0 4,256

Seq Scan on product_list_items pli2 (cost=0.00..1,807.71 rows=1 width=12) (actual time=9.879..9.879 rows=0 loops=4,256)

  • Filter: (price > '0'::double precision)
  • Rows Removed by Filter: 55577
44. 0.000 0.000 ↓ 0.0 0

Index Scan using product_url_pkey on product_url pu2 (cost=0.42..2.67 rows=1 width=4) (never executed)

  • Index Cond: (id = pli2.product_url_id)
  • Filter: ((id <> rsri_3.url_id) AND (upc = (CASE WHEN ((COALESCE(NULLIF(btrim(pu.upc), ''::text), (pu.customer_uploaded_upc)::text) <> ''::text) AND (COALESCE(NULLIF(btrim(pu.upc), ''::text), (pu.customer_uploaded_upc)::text) IS NOT NULL)) THEN (replace(COALESCE(NULLIF(btrim(pu.upc), ''::text), (pu.customer_uploaded_upc)::text), ';'::text, '
45. 34.048 42,125.888 ↓ 0.0 0 4,256

Limit (cost=6.96..1,818.41 rows=1 width=8) (actual time=9.898..9.898 rows=0 loops=4,256)

46.          

Initplan (for Limit)

47. 0.000 0.000 ↓ 0.0 0

Limit (cost=5.43..6.54 rows=1 width=13) (never executed)

48.          

Initplan (for Limit)

49. 0.000 0.000 ↓ 0.0 0

Index Scan using product_pkey on product p2_2 (cost=0.42..2.64 rows=1 width=9) (never executed)

  • Index Cond: (id = pu.product_id)
50. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on ch_master_catalog_products cmcp_2 (cost=2.79..5.01 rows=2 width=13) (never executed)

  • Recheck Cond: ((product_url_id = pu.id) OR ((web_id)::text = ($13)::text))
  • Filter: (upc IS NOT NULL)
51. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=2.79..2.79 rows=2 width=0) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ch_master_catalog_products_product_url_id (cost=0.00..1.39 rows=1 width=0) (never executed)

  • Index Cond: (product_url_id = pu.id)
53. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ch_master_catalog_products_web_id_index (cost=0.00..1.39 rows=1 width=0) (never executed)

  • Index Cond: ((web_id)::text = ($13)::text)
54. 0.000 42,091.840 ↓ 0.0 0 4,256

Nested Loop (cost=0.42..1,811.87 rows=1 width=8) (actual time=9.890..9.890 rows=0 loops=4,256)

55. 42,091.840 42,091.840 ↓ 0.0 0 4,256

Seq Scan on product_list_items pli2_1 (cost=0.00..1,807.71 rows=1 width=12) (actual time=9.890..9.890 rows=0 loops=4,256)

  • Filter: (price > '0'::double precision)
  • Rows Removed by Filter: 55577
56. 0.000 0.000 ↓ 0.0 0

Index Scan using product_url_pkey on product_url pu2_1 (cost=0.42..2.67 rows=1 width=4) (never executed)

  • Index Cond: (id = pli2_1.product_url_id)
  • Filter: ((id <> rsri_3.url_id) AND (upc = (CASE WHEN ((COALESCE(NULLIF(btrim(pu.upc), ''::text), (pu.customer_uploaded_upc)::text) <> ''::text) AND (COALESCE(NULLIF(btrim(pu.upc), ''::text), (pu.customer_uploaded_upc)::text) IS NOT NULL)) THEN (replace(COALESCE(NULLIF(btrim(pu.upc), ''::text), (pu.customer_uploaded_upc)::text), ';'::text, '
57.          

CTE _rsri_pivot

58. 0.000 88,547.616 ↓ 1.7 15 1

Limit (cost=1.16..1.23 rows=9 width=88) (actual time=88,547.579..88,547.616 rows=15 loops=1)

59.          

Initplan (for Limit)

60. 21.511 30.774 ↑ 1.0 1 1

Aggregate (cost=0.20..0.21 rows=1 width=8) (actual time=30.773..30.774 rows=1 loops=1)

61. 9.263 9.263 ↓ 1,551.3 13,962 1

CTE Scan on _rsri (cost=0.00..0.18 rows=9 width=32) (actual time=0.003..9.263 rows=13,962 loops=1)

62. 2.435 11.614 ↑ 1.0 1 1

Aggregate (cost=0.20..0.21 rows=1 width=32) (actual time=11.613..11.614 rows=1 loops=1)

63. 9.179 9.179 ↓ 1,551.3 13,962 1

CTE Scan on _rsri _rsri_1 (cost=0.00..0.18 rows=9 width=4) (actual time=0.003..9.179 rows=13,962 loops=1)

64. 0.023 88,547.610 ↓ 1.7 15 1

Unique (cost=0.74..0.80 rows=9 width=88) (actual time=88,547.577..88,547.610 rows=15 loops=1)

65. 7.550 88,547.587 ↓ 7.4 67 1

Sort (cost=0.74..0.76 rows=9 width=88) (actual time=88,547.576..88,547.587 rows=67 loops=1)

  • Sort Key: (min(rsri_4.price) OVER (?)), rsri_4.group_hash
  • Sort Method: quicksort Memory: 2348kB
66. 52.272 88,540.037 ↓ 1,551.3 13,962 1

WindowAgg (cost=0.44..0.59 rows=9 width=88) (actual time=88,528.014..88,540.037 rows=13,962 loops=1)

67. 23.521 88,487.765 ↓ 1,551.3 13,962 1

Sort (cost=0.44..0.46 rows=9 width=49) (actual time=88,485.603..88,487.765 rows=13,962 loops=1)

  • Sort Key: rsri_4.group_hash
  • Sort Method: quicksort Memory: 2263kB
68. 30.261 88,464.244 ↓ 1,551.3 13,962 1

WindowAgg (cost=0.00..0.29 rows=9 width=49) (actual time=88,455.062..88,464.244 rows=13,962 loops=1)

69. 88,433.983 88,433.983 ↓ 1,551.3 13,962 1

CTE Scan on _rsri rsri_4 (cost=0.00..0.18 rows=9 width=41) (actual time=88,302.916..88,433.983 rows=13,962 loops=1)

70.          

CTE _rsri_limited

71. 0.143 88,573.379 ↓ 8.0 72 1

Sort (cost=1.04..1.06 rows=9 width=507) (actual time=88,573.365..88,573.379 rows=72 loops=1)

  • Sort Key: (min(rsri_5.price) OVER (?)), rsri_5.group_hash
  • Sort Method: quicksort Memory: 98kB
72. 0.109 88,573.236 ↓ 8.0 72 1

WindowAgg (cost=0.74..0.90 rows=9 width=507) (actual time=88,573.135..88,573.236 rows=72 loops=1)

73. 0.102 88,573.127 ↓ 8.0 72 1

Sort (cost=0.74..0.76 rows=9 width=499) (actual time=88,573.121..88,573.127 rows=72 loops=1)

  • Sort Key: rsri_5.group_hash
  • Sort Method: quicksort Memory: 98kB
74. 0.074 88,573.025 ↓ 8.0 72 1

Hash Join (cost=0.29..0.60 rows=9 width=499) (actual time=88,572.923..88,573.025 rows=72 loops=1)

  • Hash Cond: ((_rsri_pivot.group_hash)::text = (rsri_5.group_hash)::text)
75. 88,547.626 88,547.626 ↓ 1.7 15 1

CTE Scan on _rsri_pivot (cost=0.00..0.18 rows=9 width=80) (actual time=88,547.581..88,547.626 rows=15 loops=1)

76. 13.934 25.325 ↓ 1,551.3 13,962 1

Hash (cost=0.18..0.18 rows=9 width=451) (actual time=25.325..25.325 rows=13,962 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 11085kB
77. 11.391 11.391 ↓ 1,551.3 13,962 1

CTE Scan on _rsri rsri_5 (cost=0.00..0.18 rows=9 width=451) (actual time=0.003..11.391 rows=13,962 loops=1)

78. 0.867 88,578.445 ↓ 7.2 187 1

Sort (cost=142.67..142.73 rows=26 width=690) (actual time=88,578.430..88,578.445 rows=187 loops=1)

  • Sort Key: (min(rsri.price) OVER (?)), rsri.group_hash, rsri.product_unique_id, rsri.date_of_upload, pm.id
  • Sort Method: quicksort Memory: 240kB
79. 0.266 88,577.578 ↓ 7.2 187 1

WindowAgg (cost=25.81..142.06 rows=26 width=690) (actual time=88,576.964..88,577.578 rows=187 loops=1)

80. 0.270 88,576.938 ↓ 7.2 187 1

Sort (cost=25.81..25.88 rows=26 width=573) (actual time=88,576.920..88,576.938 rows=187 loops=1)

  • Sort Key: rsri.group_hash
  • Sort Method: quicksort Memory: 216kB
81. 0.236 88,576.668 ↓ 7.2 187 1

Nested Loop Left Join (cost=1.43..25.20 rows=26 width=573) (actual time=88,573.591..88,576.668 rows=187 loops=1)

82. 0.116 88,575.684 ↓ 7.2 187 1

Nested Loop Left Join (cost=1.14..17.21 rows=26 width=563) (actual time=88,573.555..88,575.684 rows=187 loops=1)

83. 0.053 88,574.488 ↓ 72.0 72 1

Nested Loop Left Join (cost=0.70..13.82 rows=1 width=555) (actual time=88,573.501..88,574.488 rows=72 loops=1)

84. 0.048 88,574.147 ↓ 72.0 72 1

Nested Loop (cost=0.27..11.18 rows=1 width=549) (actual time=88,573.463..88,574.147 rows=72 loops=1)

85. 0.197 88,573.883 ↓ 72.0 72 1

Nested Loop (cost=0.27..10.00 rows=1 width=517) (actual time=88,573.444..88,573.883 rows=72 loops=1)

86. 88,573.470 88,573.470 ↓ 72.0 72 1

CTE Scan on _rsri_limited rsri (cost=0.00..0.20 rows=1 width=499) (actual time=88,573.369..88,573.470 rows=72 loops=1)

  • Filter: (product_list_id = 24)
87. 0.216 0.216 ↑ 1.0 1 72

Index Scan using sites_id_pkey on sites s (cost=0.27..8.34 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=72)

  • Index Cond: ((id = rsri.site_id) AND (id = ANY ('{342,338,163,7,335,237,77,273,186,136}'::integer[])))
88. 0.216 0.216 ↑ 1.0 1 72

Seq Scan on product_list pl (cost=0.00..1.16 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=72)

  • Filter: (id = 24)
  • Rows Removed by Filter: 12
89. 0.288 0.288 ↓ 0.0 0 72

Index Scan using ranking_search_results_items_original_prices_pkey on ranking_search_results_items_original_prices op (cost=0.42..2.64 rows=1 width=10) (actual time=0.004..0.004 rows=0 loops=72)

  • Index Cond: (rsri.id = rsri_id)
90. 1.080 1.080 ↑ 13.0 2 72

Index Scan using rsri_id_index on product_marketplace pm (cost=0.44..3.13 rows=26 width=12) (actual time=0.014..0.015 rows=2 loops=72)

  • Index Cond: (rsri_id = rsri.id)
91. 0.748 0.748 ↑ 1.0 1 187

Index Scan using marketplace_pkey on marketplace m (cost=0.29..0.31 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=187)

  • Index Cond: (id = pm.marketplace_id)
92.          

SubPlan (for WindowAgg)

93. 0.187 0.374 ↓ 0.0 0 187

Limit (cost=1.27..4.45 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=187)

94. 0.000 0.187 ↓ 0.0 0 187

Bitmap Heap Scan on ranking_search_results_items_prices rsrip (cost=1.27..4.45 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=187)

  • Recheck Cond: (rsri_id = rsri.id)
  • Filter: (price > '0'::numeric)
95. 0.187 0.187 ↓ 0.0 0 187

Bitmap Index Scan on ranking_search_results_items_prices_rsri_id_idx (cost=0.00..1.27 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=187)

  • Index Cond: (rsri_id = rsri.id)
Planning time : 11.401 ms
Execution time : 88,594.780 ms