explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H54h

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 58,800.102 58,800.102 ↑ 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=58,800.017..58,800.102 rows=24 loops=1)

2.          

CTE products

3. 0.007 0.110 ↑ 1.0 9 1

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

4. 0.013 0.013 ↑ 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.013 rows=9 loops=1)

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

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

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

CTE changes

7. 0.114 6,766.857 ↑ 479.8 24 1

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

8. 0.040 6,766.743 ↑ 479.8 24 1

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

  • Sort Key: price_optimization_product_meta.product_id, price_optimization_product_meta.zero_period_start
9. 0.020 0.032 ↑ 1.0 9 1

Sort (cost=1.32..1.35 rows=9 width=23) (actual time=0.016..0.032 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.012 0.012 ↑ 1.0 9 1

Seq Scan on price_optimization_product_meta (cost=0.00..1.09 rows=9 width=23) (actual time=0.008..0.012 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.183 6,766.671 ↑ 767.0 15 1

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

12. 0.046 6,766.488 ↑ 767.0 15 1

Sort (cost=1,197,438.51..1,197,467.27 rows=11,505 width=26) (actual time=6,766.465..6,766.488 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.672 6,766.442 ↑ 767.0 15 1

Merge Join (cost=1,180,689.20..1,196,662.50 rows=11,505 width=26) (actual time=6,524.256..6,766.442 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. 107.617 6,758.734 ↑ 7.9 97,116 1

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

15. 1,210.617 6,651.117 ↓ 1.4 1,043,653 1

Sort (cost=1,180,687.97..1,182,605.53 rows=767,025 width=52) (actual time=6,240.588..6,651.117 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. 697.707 5,440.500 ↓ 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,565.518..5,440.500 rows=1,057,670 loops=1)

  • Hash Cond: (product_variant_price_changes.product_variant_id = pv.id)
17. 177.716 177.716 ↑ 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.008..177.716 rows=1,064,491 loops=1)

  • Filter: (created_at IS NOT NULL)
18. 199.405 4,565.077 ↑ 1.0 909,947 1

Hash (cost=989,766.40..989,766.40 rows=914,454 width=16) (actual time=4,565.077..4,565.077 rows=909,947 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 3,693kB
19. 4,365.672 4,365.672 ↑ 1.0 909,947 1

Seq Scan on product_variants pv (cost=0.00..989,766.40 rows=914,454 width=16) (actual time=13.196..4,365.672 rows=909,947 loops=1)

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

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

  • Sort Key: price_optimization_product_meta_1.product_id
  • Sort Method: quicksort Memory: 25kB
21. 0.016 0.016 ↑ 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.016 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.078 58,800.054 ↑ 31,344.5 24 1

WindowAgg (cost=814,052.76..847,904.77 rows=752,267 width=322) (actual time=58,800.004..58,800.054 rows=24 loops=1)

24. 0.020 58,799.976 ↑ 31,344.5 24 1

Sort (cost=814,052.76..815,933.42 rows=752,267 width=186) (actual time=58,799.975..58,799.976 rows=24 loops=1)

  • Sort Key: changes.product_id, changes.period_end
  • Sort Method: quicksort Memory: 28kB
25. 0.027 58,799.956 ↑ 31,344.5 24 1

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

26. 0.651 58,799.809 ↑ 31,344.5 24 1

Merge Left Join (cost=520,849.90..532,191.47 rows=752,267 width=282) (actual time=58,798.387..58,799.809 rows=24 loops=1)

  • Merge Cond: (products_1.master_product_id = costs.master_product_id)
27. 0.086 58,785.900 ↑ 479.8 24 1

Sort (cost=519,301.25..519,330.04 rows=11,514 width=278) (actual time=58,785.895..58,785.900 rows=24 loops=1)

  • Sort Key: products_1.master_product_id
  • Sort Method: quicksort Memory: 28kB
28. 0.262 58,785.814 ↑ 479.8 24 1

Nested Loop Left Join (cost=0.79..518,524.57 rows=11,514 width=278) (actual time=9,051.795..58,785.814 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.165 32,743.128 ↑ 479.8 24 1

Nested Loop Left Join (cost=0.54..201,889.32 rows=11,514 width=234) (actual time=7,878.964..32,743.128 rows=24 loops=1)

30. 0.123 6,767.163 ↑ 479.8 24 1

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

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

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

32. 0.004 0.136 ↑ 1.0 9 1

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

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

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

34. 25,975.800 25,975.800 ↑ 1.0 1 24

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

  • Filter: ((changes.period_start = date_start) AND (changes.period_end = date_end) AND (changes.product_id = product_id))
35. 26,042.424 26,042.424 ↑ 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=1,085.100..1,085.101 rows=1 loops=24)

36. 3.899 13.258 ↑ 1.5 8,506 1

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

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

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

38. 6.899 8.201 ↑ 1.1 11,419 1

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

  • Group Key: china_supplier_prices_for_master_products_new.master_product_id
39. 1.302 1.302 ↑ 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.014..1.302 rows=12,779 loops=1)

40. 0.120 0.120 ↑ 1.0 1 24

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

Planning time : 1.257 ms
Execution time : 58,806.805 ms