explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pndVu

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 37,342.386 ↓ 15.0 15 1

Limit (cost=45,197.97..45,197.97 rows=1 width=88) (actual time=37,342.373..37,342.386 rows=15 loops=1)

2.          

CTE _rsrimin

3. 2.987 73.900 ↓ 1.2 10,539 1

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

4. 0.004 0.004 ↓ 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.004..0.004 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. 70.909 70.909 ↓ 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.129..70.909 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.919 37,274.107 ↓ 777.2 6,995 1

Unique (cost=33,137.33..33,137.42 rows=9 width=558) (actual time=37,200.331..37,274.107 rows=6,995 loops=1)

8. 617.481 37,247.188 ↓ 6,519.9 58,679 1

Sort (cost=33,137.33..33,137.35 rows=9 width=558) (actual time=37,200.329..37,247.188 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,185.060 36,629.707 ↓ 6,519.9 58,679 1

Nested Loop Left Join (cost=2.82..33,137.18 rows=9 width=558) (actual time=0.526..36,629.707 rows=58,679 loops=1)

10. 56.016 812.481 ↓ 56,888.0 56,888 1

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

11. 18.210 486.965 ↓ 10,780.0 10,780 1

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

12. 15.952 404.075 ↓ 10,780.0 10,780 1

Nested Loop (cost=0.71..337.88 rows=1 width=483) (actual time=0.230..404.075 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. 21.563 293.272 ↓ 10,539.0 10,539 1

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

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

CTE Scan on _rsrimin rsri_3 (cost=0.00..334.88 rows=1 width=129) (actual time=0.139..113.624 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. 158.085 158.085 ↑ 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.015..0.015 rows=1 loops=10,539)

  • Index Cond: (id = rsri_3.url_id)
16. 94.851 94.851 ↑ 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.006..0.009 rows=1 loops=10,539)

  • Index Cond: (product_url_id = pu.id)
  • Filter: (product_list_id = 24)
  • Rows Removed by Filter: 1
17. 64.680 64.680 ↑ 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.006..0.006 rows=1 loops=10,780)

  • Index Cond: (id = pu.product_id)
18. 97.020 269.500 ↑ 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.020..0.025 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 172.480 ↓ 0.0 0 10,780

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

20. 43.120 43.120 ↑ 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.004..0.004 rows=3 loops=10,780)

  • Index Cond: (pu.id = first_product_url_id)
21. 43.120 43.120 ↑ 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.004..0.004 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. 227.552 227.552 ↓ 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.004..0.004 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 21.390 ↓ 0.0 0 1,426

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

27.          

Initplan (for Limit)

28. 5.704 5.704 ↑ 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.003..0.004 rows=1 loops=1,426)

  • Index Cond: (id = pu.product_id)
29. 1.426 19.964 ↓ 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.014..0.014 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. 7.130 18.538 ↓ 0.0 0 1,426

BitmapOr (cost=2.79..2.79 rows=2 width=0) (actual time=0.013..0.013 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. 8.556 8.556 ↓ 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.006..0.006 rows=0 loops=1,426)

  • Index Cond: ((web_id)::text = ($2)::text)
33. 11.064 17,173.172 ↓ 0.0 0 1,844

Limit (cost=6.96..1,818.41 rows=1 width=8) (actual time=9.313..9.313 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 17,162.108 ↓ 0.0 0 1,844

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

43. 17,160.264 17,160.264 ↓ 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.306..9.306 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 17,210.052 ↓ 0.0 0 1,844

Limit (cost=6.96..1,818.41 rows=1 width=8) (actual time=9.333..9.333 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 17,197.144 ↓ 0.0 0 1,844

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

55. 17,195.300 17,195.300 ↓ 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.325..9.325 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.          

Initplan (for Limit)

58. 11.776 16.329 ↑ 1.0 1 1

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

59. 4.553 4.553 ↓ 777.2 6,995 1

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

60. 1.269 5.787 ↑ 1.0 1 1

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

61. 4.518 4.518 ↓ 777.2 6,995 1

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

62. 0.017 37,342.380 ↓ 3.3 30 1

Unique (cost=0.74..0.80 rows=9 width=88) (actual time=37,342.358..37,342.380 rows=30 loops=1)

63. 4.204 37,342.363 ↓ 6.9 62 1

Sort (cost=0.74..0.76 rows=9 width=88) (actual time=37,342.357..37,342.363 rows=62 loops=1)

  • Sort Key: (min(rsri.price) OVER (?)), rsri.group_hash
  • Sort Method: quicksort Memory: 1176kB
64. 27.577 37,338.159 ↓ 777.2 6,995 1

WindowAgg (cost=0.44..0.59 rows=9 width=88) (actual time=37,331.441..37,338.159 rows=6,995 loops=1)

65. 12.632 37,310.582 ↓ 777.2 6,995 1

Sort (cost=0.44..0.46 rows=9 width=49) (actual time=37,309.307..37,310.582 rows=6,995 loops=1)

  • Sort Key: rsri.group_hash
  • Sort Method: quicksort Memory: 1132kB
66. 15.567 37,297.950 ↓ 777.2 6,995 1

WindowAgg (cost=0.00..0.29 rows=9 width=49) (actual time=37,293.436..37,297.950 rows=6,995 loops=1)

67. 37,282.383 37,282.383 ↓ 777.2 6,995 1

CTE Scan on _rsri rsri (cost=0.00..0.18 rows=9 width=41) (actual time=37,200.339..37,282.383 rows=6,995 loops=1)

Planning time : 25.189 ms
Execution time : 37,353.421 ms