explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9C1r : Optimization for: plan #H54h

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 8,977.111 8,977.111 ↑ 31,344.5 24 1

CTE Scan on raw (cost=2,046,284.90..2,068,852.91 rows=752,267 width=326) (actual time=8,976.996..8,977.111 rows=24 loops=1)

2.          

CTE products

3. 0.009 0.177 ↑ 1.0 9 1

Nested Loop (cost=0.42..77.10 rows=9 width=1,657) (actual time=0.037..0.177 rows=9 loops=1)

4. 0.015 0.015 ↑ 1.0 9 1

Seq Scan on price_optimization_product_meta popm (cost=0.00..1.09 rows=9 width=8) (actual time=0.011..0.015 rows=9 loops=1)

  • Filter: approved
5. 0.153 0.153 ↑ 1.0 1 9

Index Scan using products_pkey on products (cost=0.42..8.44 rows=1 width=1,657) (actual time=0.017..0.017 rows=1 loops=9)

  • Index Cond: (id = popm.product_id)
6.          

CTE changes

7. 0.114 6,903.579 ↑ 479.8 24 1

WindowAgg (cost=1,197,439.84..1,198,303.03 rows=11,514 width=30) (actual time=6,903.267..6,903.579 rows=24 loops=1)

8. 0.038 6,903.465 ↑ 479.8 24 1

Merge Append (cost=1,197,439.84..1,198,072.75 rows=11,514 width=22) (actual time=6,903.262..6,903.465 rows=24 loops=1)

  • Sort Key: price_optimization_product_meta.product_id, price_optimization_product_meta.zero_period_start
9. 0.025 0.038 ↑ 1.0 9 1

Sort (cost=1.32..1.35 rows=9 width=23) (actual time=0.022..0.038 rows=9 loops=1)

  • Sort Key: price_optimization_product_meta.product_id, price_optimization_product_meta.zero_period_start
  • Sort Method: quicksort Memory: 25kB
10. 0.013 0.013 ↑ 1.0 9 1

Seq Scan on price_optimization_product_meta (cost=0.00..1.09 rows=9 width=23) (actual time=0.010..0.013 rows=9 loops=1)

  • Filter: ((zero_period_price IS NOT NULL) AND (zero_period_end IS NOT NULL) AND (zero_period_start IS NOT NULL))
11. 0.154 6,903.389 ↑ 767.0 15 1

WindowAgg (cost=1,197,438.51..1,197,812.42 rows=11,505 width=22) (actual time=6,903.238..6,903.389 rows=15 loops=1)

12. 0.040 6,903.235 ↑ 767.0 15 1

Sort (cost=1,197,438.51..1,197,467.27 rows=11,505 width=26) (actual time=6,903.218..6,903.235 rows=15 loops=1)

  • Sort Key: price_optimization_product_meta_1.product_id, ((product_variant_price_changes.created_at)::date)
  • Sort Method: quicksort Memory: 26kB
13. 7.769 6,903.195 ↑ 767.0 15 1

Merge Join (cost=1,180,689.20..1,196,662.50 rows=11,505 width=26) (actual time=6,645.391..6,903.195 rows=15 loops=1)

  • Merge Cond: (pv.product_id = price_optimization_product_meta_1.product_id)
  • Join Filter: (product_variant_price_changes.created_at > price_optimization_product_meta_1.zero_period_end)
  • Rows Removed by Join Filter: 20
14. 110.083 6,895.393 ↑ 7.9 97,116 1

Unique (cost=1,180,687.97..1,186,440.66 rows=767,025 width=52) (actual time=6,352.821..6,895.393 rows=97,116 loops=1)

15. 1,271.811 6,785.310 ↓ 1.4 1,043,653 1

Sort (cost=1,180,687.97..1,182,605.53 rows=767,025 width=52) (actual time=6,352.819..6,785.310 rows=1,043,653 loops=1)

  • Sort Key: pv.product_id, product_variant_price_changes.created_at
  • Sort Method: external merge Disk: 35,120kB
16. 719.622 5,513.499 ↓ 1.4 1,057,670 1

Hash Join (cost=1,005,663.08..1,053,278.44 rows=767,025 width=52) (actual time=4,619.042..5,513.499 rows=1,057,670 loops=1)

  • Hash Cond: (product_variant_price_changes.product_variant_id = pv.id)
17. 175.319 175.319 ↑ 1.0 1,064,491 1

Seq Scan on product_variant_price_changes (cost=0.00..18,873.63 rows=1,073,463 width=22) (actual time=0.009..175.319 rows=1,064,491 loops=1)

  • Filter: (created_at IS NOT NULL)
18. 174.032 4,618.558 ↑ 1.0 910,010 1

Hash (cost=989,766.40..989,766.40 rows=914,454 width=16) (actual time=4,618.558..4,618.558 rows=910,010 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 3,693kB
19. 4,444.526 4,444.526 ↑ 1.0 910,010 1

Seq Scan on product_variants pv (cost=0.00..989,766.40 rows=914,454 width=16) (actual time=0.014..4,444.526 rows=910,010 loops=1)

  • Filter: ((status)::text = 'active'::text)
  • Rows Removed by Filter: 359,264
20. 0.019 0.033 ↓ 3.4 31 1

Sort (cost=1.23..1.26 rows=9 width=12) (actual time=0.026..0.033 rows=31 loops=1)

  • Sort Key: price_optimization_product_meta_1.product_id
  • Sort Method: quicksort Memory: 25kB
21. 0.014 0.014 ↑ 1.0 9 1

Seq Scan on price_optimization_product_meta price_optimization_product_meta_1 (cost=0.00..1.09 rows=9 width=12) (actual time=0.012..0.014 rows=9 loops=1)

  • Filter: ((zero_period_price IS NOT NULL) AND (zero_period_end IS NOT NULL) AND (zero_period_start IS NOT NULL))
22.          

CTE raw

23. 0.112 8,977.072 ↑ 31,344.5 24 1

WindowAgg (cost=814,052.76..847,904.77 rows=752,267 width=322) (actual time=8,976.985..8,977.072 rows=24 loops=1)

24. 0.019 8,976.960 ↑ 31,344.5 24 1

Sort (cost=814,052.76..815,933.42 rows=752,267 width=186) (actual time=8,976.959..8,976.960 rows=24 loops=1)

  • Sort Key: changes.product_id, changes.period_end
  • Sort Method: quicksort Memory: 28kB
25. 0.010 8,976.941 ↑ 31,344.5 24 1

Nested Loop Left Join (cost=520,849.90..601,776.17 rows=752,267 width=186) (actual time=8,975.371..8,976.941 rows=24 loops=1)

26. 0.642 8,976.787 ↑ 31,344.5 24 1

Merge Left Join (cost=520,849.90..532,191.47 rows=752,267 width=282) (actual time=8,975.326..8,976.787 rows=24 loops=1)

  • Merge Cond: (products_1.master_product_id = costs.master_product_id)
27. 0.123 8,963.099 ↑ 479.8 24 1

Sort (cost=519,301.25..519,330.04 rows=11,514 width=278) (actual time=8,963.096..8,963.099 rows=24 loops=1)

  • Sort Key: products_1.master_product_id
  • Sort Method: quicksort Memory: 28kB
28. 0.160 8,962.976 ↑ 479.8 24 1

Nested Loop Left Join (cost=0.79..518,524.57 rows=11,514 width=278) (actual time=7,077.179..8,962.976 rows=24 loops=1)

  • Join Filter: ((changes.period_start = product_agg.date_start) AND (changes.period_end = product_agg.date_end) AND (changes.product_id = product_agg.product_id))
29. 0.149 6,910.336 ↑ 479.8 24 1

Nested Loop Left Join (cost=0.54..201,889.32 rows=11,514 width=234) (actual time=6,905.091..6,910.336 rows=24 loops=1)

30. 0.116 6,903.947 ↑ 479.8 24 1

Hash Left Join (cost=0.29..278.93 rows=11,514 width=46) (actual time=6,903.491..6,903.947 rows=24 loops=1)

  • Hash Cond: (changes.product_id = products_1.id)
31. 6,903.624 6,903.624 ↑ 479.8 24 1

CTE Scan on changes (cost=0.00..230.28 rows=11,514 width=38) (actual time=6,903.269..6,903.624 rows=24 loops=1)

32. 0.004 0.207 ↑ 1.0 9 1

Hash (cost=0.18..0.18 rows=9 width=16) (actual time=0.207..0.207 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.203 0.203 ↑ 1.0 9 1

CTE Scan on products products_1 (cost=0.00..0.18 rows=9 width=16) (actual time=0.043..0.203 rows=9 loops=1)

34. 6.240 6.240 ↑ 1.0 1 24

Function Scan on daily_product_performance_agg product_agg (cost=0.25..17.75 rows=1 width=188) (actual time=0.259..0.260 rows=1 loops=24)

  • Filter: ((changes.period_start = date_start) AND (changes.period_end = date_end) AND (changes.product_id = product_id))
35. 2,052.480 2,052.480 ↑ 1,000.0 1 24

Function Scan on daily_store_performance_agg store_agg (cost=0.25..10.25 rows=1,000 width=64) (actual time=85.520..85.520 rows=1 loops=24)

36. 3.888 13.046 ↑ 1.5 8,506 1

Sort (cost=1,548.64..1,581.31 rows=13,067 width=12) (actual time=12.194..13.046 rows=8,506 loops=1)

  • Sort Key: costs.master_product_id
  • Sort Method: quicksort Memory: 920kB
37. 1.258 9.158 ↑ 1.1 11,419 1

Subquery Scan on costs (cost=328.60..655.28 rows=13,067 width=12) (actual time=5.174..9.158 rows=11,419 loops=1)

38. 6.673 7.900 ↑ 1.1 11,419 1

HashAggregate (cost=328.60..524.61 rows=13,067 width=12) (actual time=5.172..7.900 rows=11,419 loops=1)

  • Group Key: china_supplier_prices_for_master_products_new.master_product_id
39. 1.227 1.227 ↑ 1.2 12,779 1

Seq Scan on china_supplier_prices_for_master_products_new (cost=0.00..254.40 rows=14,840 width=12) (actual time=0.012..1.227 rows=12,779 loops=1)

40. 0.144 0.144 ↑ 1.0 1 24

Result (cost=0.00..0.07 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=24)

Planning time : 1.783 ms
Execution time : 8,985.186 ms