explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2a4X

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 105,508.081 ↓ 0.0 0 1

Limit (cost=353,427.21..353,427.26 rows=1 width=32) (actual time=105,508.081..105,508.081 rows=0 loops=1)

2.          

CTE _cached

3. 613.751 12,106.929 ↓ 4.3 931,726 1

Group (cost=279,785.62..283,364.67 rows=215,994 width=468) (actual time=10,418.929..12,106.929 rows=931,726 loops=1)

  • Group Key: oosv.url_id, oosv.pc_nine, oosv.date, oosv.size_grid_file_id
4. 4,013.952 11,493.178 ↓ 3.3 931,726 1

Sort (cost=279,785.62..280,501.43 rows=286,324 width=468) (actual time=10,418.925..11,493.178 rows=931,726 loops=1)

  • Sort Key: oosv.url_id, oosv.pc_nine, oosv.date, oosv.size_grid_file_id
  • Sort Method: external merge Disk: 400072kB
5. 7,479.226 7,479.226 ↓ 3.3 931,726 1

Index Scan using cached_reports_oosv_date on cached_reports_oosv oosv (cost=0.43..182,752.53 rows=286,324 width=468) (actual time=0.067..7,479.226 rows=931,726 loops=1)

  • Index Cond: (date = ANY ('{2018-07-11,2018-07-12,2018-07-13,2018-07-14,2018-07-15,2018-07-16,2018-07-17,2018-07-18,2018-07-19,2018-07-20,2018-07-21,2018-07-22,2018-07-23,2018-07-24,2018-07-25,2018-07-26,2018-07-27,2018-07-28,2018-07-29,2018-07-31,2018-08-01,2018-08-02,2018-08-03,2018-08-04,2018-08-05,2018-08-06,2018-08-07,2018-08-08,2018-08-09,2018-08-10,2018-08-11,2018-08-12,2018-08-13,2018-08-14,2018-08-15,2018-08-16,2018-08-17,2018-08-18,2018-08-19,2018-08-20,2018-08-21,2018-08-22,2018-08-23,2018-08-24,2018-08-25,2018-08-26,2018-08-27,2018-08-28,2018-08-29,2018-08-30,2018-08-31,2018-09-01,2018-09-02,2018-09-03,2018-09-04,2018-09-05,2018-09-06,2018-09-07,2018-09-08,2018-09-09,2018-09-10,2018-09-11,2018-09-12,2018-09-13,2018-09-14,2018-09-15,2018-09-16,2018-09-17,2018-09-18,2018-09-19,2018-09-20,2018-09-21,2018-09-22,2018-09-23,2018-09-24,2018-09-25,2018-09-26,2018-09-27,2018-09-28,2018-09-29,2018-09-30,2018-10-01,2018-10-02,2018-10-03,2018-10-04,2018-10-05,2018-10-06,2018-10-07,2018-10-08,2018-10-09,2018-10-10,2018-10-11,2018-10-12,2018-10-13,2018-10-14,2018-10-15,2018-10-16,2018-10-17,2018-10-18,2018-10-19,2018-10-20,2018-10-21,2018-10-22,2018-10-23,2018-10-24,2018-10-25,2018-10-26,2018-10-27,2018-10-28,2018-10-29,2018-10-30,2018-10-31,2018-11-01,2018-11-02,2018-11-03,2018-11-04,2018-11-05,2018-11-06,2018-11-07,2018-11-08,2018-11-09,2018-11-10,2018-11-11,2018-11-12,2018-11-13,2018-11-14,2018-11-15,2018-11-16,2018-11-17,2018-11-18,2018-11-19,2018-11-20,2018-11-21,2018-11-22,2018-11-23,2018-11-24,2018-11-25,2018-11-26,2018-11-27,2018-11-28,2018-11-29,2018-11-30,2018-12-01,2018-12-02,2018-12-03,2018-12-04,2018-12-05,2018-12-06,2018-12-07,2018-12-08,2018-12-09,2018-12-10,2018-12-11,2018-12-12,2018-12-13,2018-12-14,2018-12-15,2018-12-16,2018-12-17,2018-12-18,2018-12-19,2018-12-20,2018-12-21,2018-12-22,2018-12-23,2018-12-24,2018-12-25,2018-12-26,2018-12-27,2018-12-28,2018-12-29,2018-12-30,2018-12-31,2019-01-01,2019-01-02,2019-01-03,2019-01-04,2019-01-05,2019-01-07,2019-01-08,2019-01-09,2019-01-10,2019-01-11,2019-01-12,2019-01-13,2019-01-14,2019-01-15,2019-01-16,2019-01-17,2019-01-18,2019-01-19,2019-01-20,2019-01-21,2019-01-22,2019-01-23,2019-01-24,2019-01-25,2019-01-26,2019-01-27,2019-01-28,2019-01-29,2019-01-30,2019-01-31,2019-02-01,2019-02-02,2019-02-03,2019-02-04,2019-02-05,2019-02-06,2019-02-07,2019-02-08,2019-02-09,2019-02-10,2019-02-11,2019-02-12,2019-02-13,2019-02-14,2019-02-15,2019-02-16,2019-02-17,2019-02-18,2019-02-19,2019-02-20,2019-02-21,2019-02-22,2019-02-23,2019-02-24,2019-02-25,2019-02-26,2019-02-27,2019-02-28,2019-03-01,2019-03-02,2019-03-03,2019-03-04,2019-03-05,2019-03-06,2019-03-07,2019-03-08,2019-03-09,2019-03-10,2019-03-11,2019-03-12,2019-03-13,2019-03-14,2019-03-15,2019-03-16,2019-03-17,2019-03-18,2019-03-19,2019-03-20,2019-03-21,2019-03-22,2019-03-23,2019-03-24,2019-03-25,2019-03-26,2019-03-27,2019-03-28,2019-03-29,2019-03-30,2019-03-31,2019-04-01,2019-04-02,2019-04-03,2019-04-04,2019-04-05,2019-04-06,2019-04-07,2019-04-08,2019-04-09,2019-04-10,2019-04-11,2019-04-12,2019-04-13,2019-04-14,2019-04-15,2019-04-16,2019-04-17,2019-04-18,2019-04-19,2019-04-20,2019-04-21,2019-04-22,2019-04-23,2019-04-24,2019-04-25,2019-04-26,2019-04-27,2019-04-28,2019-04-29,2019-04-30,2019-05-01,2019-05-02,2019-05-03,2019-05-04,2019-05-05,2019-05-06,2019-05-07,2019-05-08,2019-05-09,2019-05-10,2019-05-11,2019-05-12,2019-05-13,2019-05-14,2019-05-15,2019-05-16,2019-05-17,2019-05-18,2019-05-19,2019-05-20,2019-05-21,2019-05-22,2019-05-23,2019-05-24,2019-05-25,2019-05-26,2019-05-27,2019-05-28,2019-05-29,2019-05-30,2019-05-31,2019-06-01,2019-06-02,2019-06-03,2019-06-04,2019-06-05,2019-06-06,2019-06-07,2019-06-08,2019-06-09,2019-06-10,2019-06-11,2019-06-12,2019-06-13,2019-06-14,2019-06-15,2019-06-16,2019-06-17,2019-06-18,2019-06-19,2019-06-20,2019-06-21,2019-06-22,2019-06-23,2019-06-24,2019-06-25,2019-06-26,2019-06-27,2019-06-28,2019-06-29,2019-06-30,2019-07-01,2019-07-02,2019-07-03,2019-07-04,2019-07-05,2019-07-06,2019-07-07,2019-07-08,2019-07-09}'::date[]))
  • Filter: (is_pc_nine AND (((data #>> '{counters,total}'::text[]))::integer > 0))
  • Rows Removed by Filter: 931726
6.          

CTE _oosv

7. 0.001 105,508.075 ↓ 0.0 0 1

Unique (cost=70,061.71..70,062.54 rows=83 width=64) (actual time=105,508.075..105,508.075 rows=0 loops=1)

8. 0.013 105,508.074 ↓ 0.0 0 1

Sort (cost=70,061.71..70,061.92 rows=83 width=64) (actual time=105,508.074..105,508.074 rows=0 loops=1)

  • Sort Key: oosv_1.url_id, oosv_1.pc_nine, oosv_1.date DESC
  • Sort Method: quicksort Memory: 25kB
9. 0.001 105,508.061 ↓ 0.0 0 1

Nested Loop (cost=1,397.66..70,059.07 rows=83 width=64) (actual time=105,508.061..105,508.061 rows=0 loops=1)

10. 282.180 105,508.060 ↓ 0.0 0 1

Hash Join (cost=1,397.23..69,777.85 rows=92 width=80) (actual time=105,508.060..105,508.060 rows=0 loops=1)

  • Hash Cond: (rsri.url_id = pu.id)
  • Join Filter: ((pli.from_date <= rsri.date_of_upload) AND (pli.to_date >= rsri.date_of_upload))
11. 1,093.221 105,225.264 ↑ 16.3 931,726 1

Nested Loop (cost=1.38..11,370.92 rows=15,199,658 width=88) (actual time=10,419.158..105,225.264 rows=931,726 loops=1)

12. 365.718 20,276.703 ↓ 5,176.3 931,726 1

Nested Loop (cost=1.38..6,932.30 rows=180 width=80) (actual time=10,418.981..20,276.703 rows=931,726 loops=1)

  • Join Filter: (NOT (oosv_1.size_grid_file_id IS DISTINCT FROM COALESCE(pl.size_grid_file_id, 0)))
13. 0.021 0.035 ↑ 1.0 1 1

Bitmap Heap Scan on product_list pl (cost=1.38..2.49 rows=1 width=8) (actual time=0.033..0.035 rows=1 loops=1)

  • Recheck Cond: (id = 228)
  • Heap Blocks: exact=1
14. 0.014 0.014 ↑ 1.0 1 1

Bitmap Index Scan on product_list_pkey (cost=0.00..1.38 rows=1 width=0) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (id = 228)
15. 19,910.950 19,910.950 ↓ 25.9 931,726 1

CTE Scan on _cached oosv_1 (cost=0.00..6,479.82 rows=35,999 width=80) (actual time=10,418.943..19,910.950 rows=931,726 loops=1)

  • Filter: (is_pc_nine AND (((data #>> '{counters,total}'::text[]))::integer > 0))
16. 54,971.834 83,855.340 ↑ 12.0 1 931,726

Append (cost=0.00..24.54 rows=12 width=12) (actual time=0.070..0.090 rows=1 loops=931,726)

17. 0.000 0.000 ↓ 0.0 0 931,726

Seq Scan on ranking_search_results_items rsri (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=931,726)

  • Filter: ((search_term_id IS NULL) AND (((oosv_1.data ->> 'rsri_id'::text))::integer = id))
18. 931.726 931.726 ↓ 0.0 0 931,726

Index Scan using ranking_search_results_items_2017_1_pkey on ranking_search_results_items_2017_1 rsri_1 (cost=0.15..0.83 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=931,726)

  • Index Cond: (id = ((oosv_1.data ->> 'rsri_id'::text))::integer)
  • Filter: (search_term_id IS NULL)
19. 1,863.452 1,863.452 ↓ 0.0 0 931,726

Index Scan using ranking_search_results_items_2017_2_pkey on ranking_search_results_items_2017_2 rsri_2 (cost=0.43..2.00 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=931,726)

  • Index Cond: (id = ((oosv_1.data ->> 'rsri_id'::text))::integer)
  • Filter: (search_term_id IS NULL)
20. 1,863.452 1,863.452 ↓ 0.0 0 931,726

Index Scan using ranking_search_results_items_2017_3_pkey on ranking_search_results_items_2017_3 rsri_3 (cost=0.44..2.14 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=931,726)

  • Index Cond: (id = ((oosv_1.data ->> 'rsri_id'::text))::integer)
  • Filter: (search_term_id IS NULL)
21. 1,863.452 1,863.452 ↓ 0.0 0 931,726

Index Scan using ranking_search_results_items_2017_4_pkey on ranking_search_results_items_2017_4 rsri_4 (cost=0.45..2.42 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=931,726)

  • Index Cond: (id = ((oosv_1.data ->> 'rsri_id'::text))::integer)
  • Filter: (search_term_id IS NULL)
22. 1,863.452 1,863.452 ↓ 0.0 0 931,726

Index Scan using ranking_search_results_items_2018_1_pkey on ranking_search_results_items_2018_1 rsri_5 (cost=0.45..2.41 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=931,726)

  • Index Cond: (id = ((oosv_1.data ->> 'rsri_id'::text))::integer)
  • Filter: (search_term_id IS NULL)
23. 1,863.452 1,863.452 ↓ 0.0 0 931,726

Index Scan using ranking_search_results_items_2018_2_pkey on ranking_search_results_items_2018_2 rsri_6 (cost=0.45..2.40 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=931,726)

  • Index Cond: (id = ((oosv_1.data ->> 'rsri_id'::text))::integer)
  • Filter: (search_term_id IS NULL)
24. 3,726.904 3,726.904 ↓ 0.0 0 931,726

Index Scan using ranking_search_results_items_2018_3_pkey on ranking_search_results_items_2018_3 rsri_7 (cost=0.45..2.42 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=931,726)

  • Index Cond: (id = ((oosv_1.data ->> 'rsri_id'::text))::integer)
  • Filter: (search_term_id IS NULL)
25. 3,726.904 3,726.904 ↓ 0.0 0 931,726

Index Scan using ranking_search_results_items_2018_4_pkey on ranking_search_results_items_2018_4 rsri_8 (cost=0.45..2.45 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=931,726)

  • Index Cond: (id = ((oosv_1.data ->> 'rsri_id'::text))::integer)
  • Filter: (search_term_id IS NULL)
26. 3,726.904 3,726.904 ↓ 0.0 0 931,726

Index Scan using ranking_search_results_items_2019_1_pkey on ranking_search_results_items_2019_1 rsri_9 (cost=0.45..2.49 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=931,726)

  • Index Cond: (id = ((oosv_1.data ->> 'rsri_id'::text))::integer)
  • Filter: (search_term_id IS NULL)
27. 4,658.630 4,658.630 ↓ 0.0 0 931,726

Index Scan using ranking_search_results_items_2019_2_pkey on ranking_search_results_items_2019_2 rsri_10 (cost=0.57..2.68 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=931,726)

  • Index Cond: (id = ((oosv_1.data ->> 'rsri_id'::text))::integer)
  • Filter: (search_term_id IS NULL)
28. 2,795.178 2,795.178 ↓ 0.0 0 931,726

Index Scan using ranking_search_results_items_2019_3_pkey on ranking_search_results_items_2019_3 rsri_11 (cost=0.44..2.30 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=931,726)

  • Index Cond: (id = ((oosv_1.data ->> 'rsri_id'::text))::integer)
  • Filter: (search_term_id IS NULL)
29. 0.042 0.616 ↑ 4.3 89 1

Hash (cost=1,391.08..1,391.08 rows=382 width=24) (actual time=0.616..0.616 rows=89 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
30. 0.068 0.574 ↑ 4.3 89 1

Nested Loop (cost=0.85..1,391.08 rows=382 width=24) (actual time=0.039..0.574 rows=89 loops=1)

31. 0.061 0.061 ↑ 4.3 89 1

Index Scan using product_list_items_product_list_id on product_list_items pli (cost=0.42..380.98 rows=382 width=16) (actual time=0.018..0.061 rows=89 loops=1)

  • Index Cond: (product_list_id = 228)
32. 0.445 0.445 ↑ 1.0 1 89

Index Scan using product_url_pkey on product_url pu (cost=0.43..2.64 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=89)

  • Index Cond: (id = pli.product_url_id)
33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using product_pkey on product p (cost=0.43..1.21 rows=1 width=4) (never executed)

  • Index Cond: (id = pu.product_id)
  • Heap Fetches: 0
34.          

SubPlan (for Nested Loop)

35. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.01..2.02 rows=1 width=8) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Function Scan on json_array_elements _inv (cost=0.01..1.01 rows=100 width=32) (never executed)

37. 0.002 105,508.079 ↓ 0.0 0 1

WindowAgg (cost=0.00..4.15 rows=83 width=32) (actual time=105,508.079..105,508.079 rows=0 loops=1)

38. 105,508.077 105,508.077 ↓ 0.0 0 1

CTE Scan on _oosv (cost=0.00..1.66 rows=83 width=8) (actual time=105,508.077..105,508.077 rows=0 loops=1)

Planning time : 8.319 ms
Execution time : 105,665.924 ms