explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FqALG

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 33,097.904 ↓ 35.0 35 1

Unique (cost=45,340.76..45,341.09 rows=1 width=690) (actual time=33,097.854..33,097.904 rows=35 loops=1)

2.          

CTE _rsrimin

3. 2.433 47.265 ↓ 1.2 10,539 1

Append (cost=0.00..12,059.32 rows=8,930 width=408) (actual time=0.083..47.265 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. 44.827 44.827 ↓ 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.078..44.827 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. 26.386 33,028.535 ↓ 777.2 6,995 1

Unique (cost=33,137.33..33,137.42 rows=9 width=558) (actual time=32,955.198..33,028.535 rows=6,995 loops=1)

8. 464.086 33,002.149 ↓ 6,519.9 58,679 1

Sort (cost=33,137.33..33,137.35 rows=9 width=558) (actual time=32,955.196..33,002.149 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,074.078 32,538.063 ↓ 6,519.9 58,679 1

Nested Loop Left Join (cost=2.82..33,137.18 rows=9 width=558) (actual time=0.314..32,538.063 rows=58,679 loops=1)

10. 56.753 608.227 ↓ 56,888.0 56,888 1

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

11. 11.538 335.874 ↓ 10,780.0 10,780 1

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

12. 12.731 270.436 ↓ 10,780.0 10,780 1

Nested Loop (cost=0.71..337.88 rows=1 width=483) (actual time=0.166..270.436 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. 18.085 183.932 ↓ 10,539.0 10,539 1

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

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

CTE Scan on _rsrimin rsri_3 (cost=0.00..334.88 rows=1 width=129) (actual time=0.088..70.996 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. 94.851 94.851 ↑ 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.009..0.009 rows=1 loops=10,539)

  • Index Cond: (id = rsri_3.url_id)
16. 73.773 73.773 ↑ 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.005..0.007 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 215.600 ↑ 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.016..0.020 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. 10.780 140.140 ↓ 0.0 0 10,780

BitmapOr (cost=1.25..1.25 rows=12 width=0) (actual time=0.013..0.013 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. 32.340 32.340 ↑ 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.003..0.003 rows=3 loops=10,780)

  • Index Cond: (pu.product_id = first_product_id)
23. 32.340 32.340 ↑ 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.003..0.003 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. 1.426 15.686 ↓ 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.011..0.011 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. 11.064 15,336.548 ↓ 0.0 0 1,844

Limit (cost=6.96..1,818.41 rows=1 width=8) (actual time=8.317..8.317 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. 1.844 15,325.484 ↓ 0.0 0 1,844

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

43. 15,323.640 15,323.640 ↓ 0.0 0 1,844

Seq Scan on product_list_items pli2 (cost=0.00..1,807.71 rows=1 width=12) (actual time=8.310..8.310 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 15,332.860 ↓ 0.0 0 1,844

Limit (cost=6.96..1,818.41 rows=1 width=8) (actual time=8.315..8.315 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. 0.000 15,319.952 ↓ 0.0 0 1,844

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

55. 15,319.952 15,319.952 ↓ 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=8.308..8.308 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 134.591 ↓ 15.0 15 1

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

59.          

Initplan (for Limit)

60. 11.698 16.197 ↑ 1.0 1 1

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

61. 4.499 4.499 ↓ 777.2 6,995 1

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

62. 1.248 5.715 ↑ 1.0 1 1

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

63. 4.467 4.467 ↓ 777.2 6,995 1

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

64. 0.019 134.586 ↓ 3.3 30 1

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

65. 3.961 134.567 ↓ 6.9 62 1

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

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

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

67. 12.531 103.391 ↓ 777.2 6,995 1

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

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

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

69. 78.883 78.883 ↓ 777.2 6,995 1

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

70.          

CTE _rsri_limited

71. 0.068 33,095.632 ↓ 35.0 35 1

Sort (cost=0.30..0.30 rows=1 width=507) (actual time=33,095.626..33,095.632 rows=35 loops=1)

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

WindowAgg (cost=0.27..0.29 rows=1 width=507) (actual time=33,095.508..33,095.564 rows=35 loops=1)

73. 0.063 33,095.505 ↓ 35.0 35 1

Sort (cost=0.27..0.27 rows=1 width=499) (actual time=33,095.501..33,095.505 rows=35 loops=1)

  • Sort Key: rsri_5.group_hash
  • Sort Method: quicksort Memory: 62kB
74. 1.047 33,095.442 ↓ 35.0 35 1

Hash Join (cost=0.03..0.26 rows=1 width=499) (actual time=33,089.881..33,095.442 rows=35 loops=1)

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

CTE Scan on _rsri rsri_5 (cost=0.00..0.18 rows=9 width=451) (actual time=32,955.203..32,959.785 rows=6,995 loops=1)

76. 0.010 134.610 ↓ 15.0 15 1

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

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

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

78. 0.334 33,097.860 ↓ 3.5 91 1

Sort (cost=142.49..142.55 rows=26 width=690) (actual time=33,097.853..33,097.860 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.136 33,097.526 ↓ 3.5 91 1

WindowAgg (cost=25.63..141.88 rows=26 width=690) (actual time=33,097.223..33,097.526 rows=91 loops=1)

80. 0.138 33,097.208 ↓ 3.5 91 1

Sort (cost=25.63..25.70 rows=26 width=573) (actual time=33,097.199..33,097.208 rows=91 loops=1)

  • Sort Key: rsri.group_hash
  • Sort Method: quicksort Memory: 119kB
81. 0.104 33,097.070 ↓ 3.5 91 1

Nested Loop Left Join (cost=1.43..25.02 rows=26 width=573) (actual time=33,095.719..33,097.070 rows=91 loops=1)

82. 0.057 33,096.602 ↓ 3.5 91 1

Nested Loop Left Join (cost=1.14..17.03 rows=26 width=563) (actual time=33,095.716..33,096.602 rows=91 loops=1)

83. 0.041 33,096.160 ↓ 35.0 35 1

Nested Loop Left Join (cost=0.70..13.64 rows=1 width=555) (actual time=33,095.691..33,096.160 rows=35 loops=1)

84. 0.028 33,096.014 ↓ 35.0 35 1

Nested Loop (cost=0.27..11.00 rows=1 width=549) (actual time=33,095.678..33,096.014 rows=35 loops=1)

85. 0.068 33,095.881 ↓ 35.0 35 1

Nested Loop (cost=0.27..9.82 rows=1 width=517) (actual time=33,095.664..33,095.881 rows=35 loops=1)

86. 33,095.673 33,095.673 ↓ 35.0 35 1

CTE Scan on _rsri_limited rsri (cost=0.00..0.02 rows=1 width=499) (actual time=33,095.630..33,095.673 rows=35 loops=1)

  • Filter: (product_list_id = 24)
87. 0.140 0.140 ↑ 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.004..0.004 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.105 0.105 ↓ 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.003..0.003 rows=0 loops=35)

  • Index Cond: (rsri.id = rsri_id)
90. 0.385 0.385 ↑ 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.010..0.011 rows=2 loops=35)

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

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=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 : 6.780 ms
Execution time : 33,107.111 ms