explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tksh

Settings
# exclusive inclusive rows x rows loops node
1. 0.055 36,026.299 ↓ 35.0 35 1

Unique (cost=45,340.76..45,341.09 rows=1 width=690) (actual time=36,026.237..36,026.299 rows=35 loops=1)

2.          

CTE _rsrimin

3. 2.258 39.085 ↓ 1.2 10,539 1

Append (cost=0.00..12,059.32 rows=8,930 width=408) (actual time=0.080..39.085 rows=10,539 loops=1)

4. 0.005 0.005 ↓ 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.005..0.005 rows=0 loops=1)

  • Filter: ((search_term_id IS NULL) AND (date_of_upload >= '2019-09-16'::date) AND (date_of_upload <= '2019-09-16'::date) AND (site_id = ANY ('{342,338,163,7,335,237,77,273,186,136}'::integer[])))
5. 36.822 36.822 ↓ 1.2 10,539 1

Index Scan using ranking_search_results_items__search_term_id_date_of_uploa_idx7 on ranking_search_results_items_2019_3 rsri_2 (cost=0.56..12,059.32 rows=8,929 width=408) (actual time=0.074..36.822 rows=10,539 loops=1)

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

CTE _rsri

7. 27.778 35,947.045 ↓ 777.2 6,995 1

Unique (cost=33,137.33..33,137.42 rows=9 width=558) (actual time=35,867.863..35,947.045 rows=6,995 loops=1)

8. 497.803 35,919.267 ↓ 6,519.9 58,679 1

Sort (cost=33,137.33..33,137.35 rows=9 width=558) (actual time=35,867.862..35,919.267 rows=58,679 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: 45880kB
9. 1,042.199 35,421.464 ↓ 6,519.9 58,679 1

Nested Loop Left Join (cost=2.82..33,137.18 rows=9 width=558) (actual time=0.270..35,421.464 rows=58,679 loops=1)

10. 45.264 560.199 ↓ 56,888.0 56,888 1

Nested Loop Left Join (cost=2.39..341.04 rows=1 width=525) (actual time=0.204..560.199 rows=56,888 loops=1)

11. 8.633 310.115 ↓ 10,780.0 10,780 1

Nested Loop Left Join (cost=1.13..338.33 rows=1 width=492) (actual time=0.142..310.115 rows=10,780 loops=1)

12. 19.009 247.582 ↓ 10,780.0 10,780 1

Nested Loop (cost=0.71..337.88 rows=1 width=483) (actual time=0.132..247.582 rows=10,780 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: 473
13. 19.438 165.339 ↓ 10,539.0 10,539 1

Nested Loop (cost=0.42..337.52 rows=1 width=459) (actual time=0.108..165.339 rows=10,539 loops=1)

14. 61.589 61.589 ↓ 10,539.0 10,539 1

CTE Scan on _rsrimin rsri_3 (cost=0.00..334.88 rows=1 width=129) (actual time=0.084..61.589 rows=10,539 loops=1)

  • Filter: ((search_term_id IS NULL) AND (search_term_id IS NULL) AND (date_of_upload >= '2019-09-16'::date) AND (date_of_upload <= '2019-09-16'::date) AND (site_id = ANY ('{342,338,163,7,335,237,77,273,186,136}'::integer[])))
15. 84.312 84.312 ↑ 1.0 1 10,539

Index Scan using product_url_pkey on product_url pu (cost=0.42..2.64 rows=1 width=330) (actual time=0.008..0.008 rows=1 loops=10,539)

  • Index Cond: (id = rsri_3.url_id)
16. 63.234 63.234 ↑ 1.0 1 10,539

Index Scan using pli_url_id on product_list_items pli (cost=0.29..0.35 rows=1 width=36) (actual time=0.004..0.006 rows=1 loops=10,539)

  • Index Cond: (product_url_id = pu.id)
  • Filter: (product_list_id = 24)
  • Rows Removed by Filter: 1
17. 53.900 53.900 ↑ 1.0 1 10,780

Index Scan using product_pkey on product p (cost=0.42..0.45 rows=1 width=13) (actual time=0.005..0.005 rows=1 loops=10,780)

  • Index Cond: (id = pu.product_id)
18. 75.460 204.820 ↑ 2.4 5 10,780

Bitmap Heap Scan on product_product_association ppa (cost=1.25..2.59 rows=12 width=49) (actual time=0.015..0.019 rows=5 loops=10,780)

  • 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=24107
19. 21.560 129.360 ↓ 0.0 0 10,780

BitmapOr (cost=1.25..1.25 rows=12 width=0) (actual time=0.012..0.012 rows=0 loops=10,780)

20. 32.340 32.340 ↑ 1.0 3 10,780

Bitmap Index Scan on first_product_url_id (cost=0.00..0.31 rows=3 width=0) (actual time=0.003..0.003 rows=3 loops=10,780)

  • Index Cond: (pu.id = first_product_url_id)
21. 32.340 32.340 ↑ 1.0 3 10,780

Bitmap Index Scan on second_product_url_id (cost=0.00..0.31 rows=3 width=0) (actual time=0.003..0.003 rows=3 loops=10,780)

  • Index Cond: (pu.id = second_product_url_id)
22. 21.560 21.560 ↑ 1.0 3 10,780

Bitmap Index Scan on product_product_association_first_product_id_idx (cost=0.00..0.31 rows=3 width=0) (actual time=0.002..0.002 rows=3 loops=10,780)

  • Index Cond: (pu.product_id = first_product_id)
23. 21.560 21.560 ↑ 1.0 3 10,780

Bitmap Index Scan on product_product_association_second_product_id_idx (cost=0.00..0.31 rows=3 width=0) (actual time=0.002..0.002 rows=3 loops=10,780)

  • Index Cond: (pu.product_id = second_product_id)
24. 170.664 170.664 ↓ 0.0 0 56,888

Index Scan using rsri_id on variant v (cost=0.43..5.14 rows=9 width=171) (actual time=0.003..0.003 rows=0 loops=56,888)

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

SubPlan (for Nested Loop Left Join)

26. 0.000 15.686 ↓ 0.0 0 1,426

Limit (cost=5.43..6.54 rows=1 width=13) (actual time=0.011..0.011 rows=0 loops=1,426)

27.          

Initplan (for Limit)

28. 4.278 4.278 ↑ 1.0 1 1,426

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

  • Index Cond: (id = pu.product_id)
29. 0.000 14.260 ↓ 0.0 0 1,426

Bitmap Heap Scan on ch_master_catalog_products cmcp (cost=2.79..5.01 rows=2 width=13) (actual time=0.010..0.010 rows=0 loops=1,426)

  • 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=52
30. 5.704 14.260 ↓ 0.0 0 1,426

BitmapOr (cost=2.79..2.79 rows=2 width=0) (actual time=0.010..0.010 rows=0 loops=1,426)

31. 2.852 2.852 ↓ 0.0 0 1,426

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=1,426)

  • Index Cond: (product_url_id = pu.id)
32. 5.704 5.704 ↓ 0.0 0 1,426

Bitmap Index Scan on ch_master_catalog_products_web_id_index (cost=0.00..1.39 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1,426)

  • Index Cond: ((web_id)::text = ($2)::text)
33. 12.908 16,793.308 ↓ 0.0 0 1,844

Limit (cost=6.96..1,818.41 rows=1 width=8) (actual time=9.107..9.107 rows=0 loops=1,844)

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. 0.000 16,780.400 ↓ 0.0 0 1,844

Nested Loop (cost=0.42..1,811.87 rows=1 width=8) (actual time=9.100..9.100 rows=0 loops=1,844)

43. 16,780.400 16,780.400 ↓ 0.0 0 1,844

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

  • 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. 12.908 16,839.408 ↓ 0.0 0 1,844

Limit (cost=6.96..1,818.41 rows=1 width=8) (actual time=9.132..9.132 rows=0 loops=1,844)

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. 1.844 16,826.500 ↓ 0.0 0 1,844

Nested Loop (cost=0.42..1,811.87 rows=1 width=8) (actual time=9.125..9.125 rows=0 loops=1,844)

55. 16,824.656 16,824.656 ↓ 0.0 0 1,844

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

  • 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 149.297 ↓ 15.0 15 1

Limit (cost=1.23..1.24 rows=1 width=88) (actual time=149.281..149.297 rows=15 loops=1)

59.          

Initplan (for Limit)

60. 12.375 17.385 ↑ 1.0 1 1

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

61. 5.010 5.010 ↓ 777.2 6,995 1

CTE Scan on _rsri (cost=0.00..0.18 rows=9 width=32) (actual time=0.003..5.010 rows=6,995 loops=1)

62. 1.352 6.335 ↑ 1.0 1 1

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

63. 4.983 4.983 ↓ 777.2 6,995 1

CTE Scan on _rsri _rsri_1 (cost=0.00..0.18 rows=9 width=4) (actual time=0.004..4.983 rows=6,995 loops=1)

64. 0.019 149.290 ↓ 3.3 30 1

Unique (cost=0.74..0.80 rows=9 width=88) (actual time=149.265..149.290 rows=30 loops=1)

65. 4.136 149.271 ↓ 6.9 62 1

Sort (cost=0.74..0.76 rows=9 width=88) (actual time=149.264..149.271 rows=62 loops=1)

  • Sort Key: (min(rsri_4.price) OVER (?)), rsri_4.group_hash
  • Sort Method: quicksort Memory: 1176kB
66. 29.168 145.135 ↓ 777.2 6,995 1

WindowAgg (cost=0.44..0.59 rows=9 width=88) (actual time=137.207..145.135 rows=6,995 loops=1)

67. 16.973 115.967 ↓ 777.2 6,995 1

Sort (cost=0.44..0.46 rows=9 width=49) (actual time=113.465..115.967 rows=6,995 loops=1)

  • Sort Key: rsri_4.group_hash
  • Sort Method: quicksort Memory: 1132kB
68. 13.705 98.994 ↓ 777.2 6,995 1

WindowAgg (cost=0.00..0.29 rows=9 width=49) (actual time=93.807..98.994 rows=6,995 loops=1)

69. 85.289 85.289 ↓ 777.2 6,995 1

CTE Scan on _rsri rsri_4 (cost=0.00..0.18 rows=9 width=41) (actual time=0.003..85.289 rows=6,995 loops=1)

70.          

CTE _rsri_limited

71. 0.072 36,023.473 ↓ 35.0 35 1

Sort (cost=0.30..0.30 rows=1 width=507) (actual time=36,023.465..36,023.473 rows=35 loops=1)

  • Sort Key: (min(rsri_5.price) OVER (?)), rsri_5.group_hash
  • Sort Method: quicksort Memory: 62kB
72. 0.062 36,023.401 ↓ 35.0 35 1

WindowAgg (cost=0.27..0.29 rows=1 width=507) (actual time=36,023.344..36,023.401 rows=35 loops=1)

73. 0.071 36,023.339 ↓ 35.0 35 1

Sort (cost=0.27..0.27 rows=1 width=499) (actual time=36,023.334..36,023.339 rows=35 loops=1)

  • Sort Key: rsri_5.group_hash
  • Sort Method: quicksort Memory: 62kB
74. 1.071 36,023.268 ↓ 35.0 35 1

Hash Join (cost=0.03..0.26 rows=1 width=499) (actual time=36,017.257..36,023.268 rows=35 loops=1)

  • Hash Cond: ((rsri_5.group_hash)::text = (_rsri_pivot.group_hash)::text)
75. 35,872.879 35,872.879 ↓ 777.2 6,995 1

CTE Scan on _rsri rsri_5 (cost=0.00..0.18 rows=9 width=451) (actual time=35,867.868..35,872.879 rows=6,995 loops=1)

76. 0.011 149.318 ↓ 15.0 15 1

Hash (cost=0.02..0.02 rows=1 width=80) (actual time=149.318..149.318 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
77. 149.307 149.307 ↓ 15.0 15 1

CTE Scan on _rsri_pivot (cost=0.00..0.02 rows=1 width=80) (actual time=149.284..149.307 rows=15 loops=1)

78. 0.340 36,026.244 ↓ 3.5 91 1

Sort (cost=142.49..142.55 rows=26 width=690) (actual time=36,026.237..36,026.244 rows=91 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: 133kB
79. 0.159 36,025.904 ↓ 3.5 91 1

WindowAgg (cost=25.63..141.88 rows=26 width=690) (actual time=36,025.591..36,025.904 rows=91 loops=1)

80. 0.147 36,025.563 ↓ 3.5 91 1

Sort (cost=25.63..25.70 rows=26 width=573) (actual time=36,025.553..36,025.563 rows=91 loops=1)

  • Sort Key: rsri.group_hash
  • Sort Method: quicksort Memory: 119kB
81. 0.048 36,025.416 ↓ 3.5 91 1

Nested Loop Left Join (cost=1.43..25.02 rows=26 width=573) (actual time=36,023.641..36,025.416 rows=91 loops=1)

82. 0.064 36,024.731 ↓ 3.5 91 1

Nested Loop Left Join (cost=1.14..17.03 rows=26 width=563) (actual time=36,023.637..36,024.731 rows=91 loops=1)

83. 0.015 36,024.177 ↓ 35.0 35 1

Nested Loop Left Join (cost=0.70..13.64 rows=1 width=555) (actual time=36,023.598..36,024.177 rows=35 loops=1)

84. 0.035 36,023.952 ↓ 35.0 35 1

Nested Loop (cost=0.27..11.00 rows=1 width=549) (actual time=36,023.562..36,023.952 rows=35 loops=1)

85. 0.113 36,023.812 ↓ 35.0 35 1

Nested Loop (cost=0.27..9.82 rows=1 width=517) (actual time=36,023.551..36,023.812 rows=35 loops=1)

86. 36,023.524 36,023.524 ↓ 35.0 35 1

CTE Scan on _rsri_limited rsri (cost=0.00..0.02 rows=1 width=499) (actual time=36,023.470..36,023.524 rows=35 loops=1)

  • Filter: (product_list_id = 24)
87. 0.175 0.175 ↑ 1.0 1 35

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

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

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

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

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.006..0.006 rows=0 loops=35)

  • Index Cond: (rsri.id = rsri_id)
90. 0.490 0.490 ↑ 13.0 2 35

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

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

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

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

SubPlan (for WindowAgg)

93. 0.091 0.182 ↓ 0.0 0 91

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

94. 0.000 0.091 ↓ 0.0 0 91

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=91)

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

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=91)

  • Index Cond: (rsri_id = rsri.id)
Planning time : 5.603 ms
Execution time : 36,034.941 ms