explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YF7k

Settings
# exclusive inclusive rows x rows loops node
1. 0.401 301,869.578 ↑ 2.5 3,780 1

Append (cost=87,260.64..2,162,337.06 rows=9,575 width=102) (actual time=18,846.740..301,869.578 rows=3,780 loops=1)

2.          

CTE not_allocated

3. 4.429 7.569 ↓ 1.0 1,452 1

HashAggregate (cost=376.94..391.02 rows=1,409 width=11) (actual time=7.010..7.569 rows=1,452 loops=1)

  • Group Key: inbounds.org_key, inbounds.option_key
4. 3.140 3.140 ↓ 1.2 11,414 1

Seq Scan on inbounds (cost=0.00..330.54 rows=9,279 width=11) (actual time=0.050..3.140 rows=11,414 loops=1)

  • Filter: (NOT repeat)
  • Rows Removed by Filter: 2,921
5. 0.482 20,292.545 ↑ 5.7 1,680 1

Subquery Scan on *SELECT* 1 (cost=86,869.61..1,404,622.54 rows=9,572 width=102) (actual time=18,846.739..20,292.545 rows=1,680 loops=1)

6. 606.758 20,292.063 ↑ 5.7 1,680 1

Hash Join (cost=86,869.61..1,404,502.89 rows=9,572 width=74) (actual time=18,846.736..20,292.063 rows=1,680 loops=1)

  • Hash Cond: (p.sku_key = sk.sku_key)
7. 11,115.742 19,603.661 ↓ 1.4 3,646,020 1

Bitmap Heap Scan on proposals p (cost=72,390.90..1,379,838.40 rows=2,671,531 width=16) (actual time=8,548.445..19,603.661 rows=3,646,020 loops=1)

  • Recheck Cond: (date_key = (now())::date)
  • Filter: (org_key = 2)
  • Rows Removed by Filter: 1,434,508
  • Heap Blocks: exact=37,341
8. 8,487.919 8,487.919 ↓ 1.4 5,080,528 1

Bitmap Index Scan on proposals_date_idx (cost=0.00..71,723.01 rows=3,653,125 width=0) (actual time=8,487.919..8,487.919 rows=5,080,528 loops=1)

  • Index Cond: (date_key = (now())::date)
9. 0.012 81.644 ↑ 238.4 8 1

Hash (cost=14,454.88..14,454.88 rows=1,907 width=303) (actual time=81.644..81.644 rows=8 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 21kB
10. 0.000 81.632 ↑ 238.4 8 1

Hash Anti Join (cost=1,032.23..14,454.88 rows=1,907 width=303) (actual time=81.113..81.632 rows=8 loops=1)

  • Hash Cond: ((sk.org_key = na.org_key) AND (sk.option_key = na.option_key))
11. 4.531 73.432 ↑ 119.2 16 1

Gather (cost=1,000.42..14,393.99 rows=1,907 width=311) (actual time=72.834..73.432 rows=16 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
12. 0.010 68.901 ↑ 140.2 8 2 / 2

Nested Loop (cost=0.42..13,203.29 rows=1,122 width=311) (actual time=63.252..68.901 rows=8 loops=2)

13. 68.157 68.157 ↑ 346.0 1 2 / 2

Parallel Seq Scan on options o (cost=0.00..2,362.49 rows=346 width=10) (actual time=62.761..68.157 rows=1 loops=2)

  • Filter: (stock_active AND (org_key = 2))
  • Rows Removed by Filter: 65,424
14. 0.734 0.734 ↑ 1.4 8 2 / 2

Index Scan using sku_option_idx on skus sk (cost=0.42..31.22 rows=11 width=311) (actual time=0.484..0.734 rows=8 loops=2)

  • Index Cond: ((org_key = 2) AND (option_key = o.option_key))
15. 0.146 8.252 ↓ 76.9 538 1

Hash (cost=31.70..31.70 rows=7 width=34) (actual time=8.251..8.252 rows=538 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
16. 8.106 8.106 ↓ 76.9 538 1

CTE Scan on not_allocated na (cost=0.00..31.70 rows=7 width=34) (actual time=7.015..8.106 rows=538 loops=1)

  • Filter: (org_key = 2)
  • Rows Removed by Filter: 914
17. 1.256 270,220.966 ↓ 1,680.0 1,680 1

GroupAggregate (cost=704,481.21..704,481.26 rows=1 width=102) (actual time=270,219.617..270,220.966 rows=1,680 loops=1)

  • Group Key: p_1.store_key, p_1.sku_key, ((sk_1.properties ->> 'BK_Article'::text))
18. 5.083 270,219.710 ↓ 1,680.0 1,680 1

Sort (cost=704,481.21..704,481.22 rows=1 width=54) (actual time=270,219.597..270,219.710 rows=1,680 loops=1)

  • Sort Key: p_1.store_key, p_1.sku_key, ((sk_1.properties ->> 'BK_Article'::text))
  • Sort Method: quicksort Memory: 180kB
19. 15.952 270,214.627 ↓ 1,680.0 1,680 1

Nested Loop (cost=195,869.09..704,481.20 rows=1 width=54) (actual time=7,121.827..270,214.627 rows=1,680 loops=1)

20. 51,334.107 270,171.795 ↓ 1,680.0 1,680 1

Nested Loop Left Join (cost=195,868.81..704,478.90 rows=1 width=321) (actual time=7,121.374..270,171.795 rows=1,680 loops=1)

  • Join Filter: ((p_1.org_key = i.org_key) AND (p_1.inbound_key = i.inbound_key) AND (p_1.store_key = pp.store_key) AND (p_1.sku_key = i.sku_key))
  • Rows Removed by Join Filter: 359,152,290
21. 138.737 4,526.808 ↓ 1,680.0 1,680 1

Hash Join (cost=27,742.68..536,352.70 rows=1 width=319) (actual time=3,486.596..4,526.808 rows=1,680 loops=1)

  • Hash Cond: (p_1.sku_key = sk_1.sku_key)
22. 1,612.475 4,272.148 ↓ 1.0 972,300 1

Bitmap Heap Scan on inbounds_proposals p_1 (cost=27,620.54..532,697.74 rows=942,081 width=20) (actual time=2,662.570..4,272.148 rows=972,300 loops=1)

  • Recheck Cond: (date_key = (now())::date)
  • Filter: (org_key = 2)
  • Rows Removed by Filter: 551,450
  • Heap Blocks: exact=12,178
23. 2,659.673 2,659.673 ↓ 1.1 1,523,750 1

Bitmap Index Scan on inbounds_proposals_date_idx (cost=0.00..27,385.02 rows=1,379,260 width=0) (actual time=2,659.673..2,659.673 rows=1,523,750 loops=1)

  • Index Cond: (date_key = (now())::date)
24. 0.034 115.923 ↓ 8.0 8 1

Hash (cost=122.12..122.12 rows=1 width=303) (actual time=115.923..115.923 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
25. 0.005 115.889 ↓ 8.0 8 1

Nested Loop (cost=0.84..122.12 rows=1 width=303) (actual time=12.791..115.889 rows=8 loops=1)

26. 0.356 115.853 ↑ 1.0 1 1

Nested Loop (cost=0.42..90.79 rows=1 width=44) (actual time=12.760..115.853 rows=1 loops=1)

27. 0.365 0.365 ↓ 76.9 538 1

CTE Scan on not_allocated na_1 (cost=0.00..31.70 rows=7 width=34) (actual time=0.004..0.365 rows=538 loops=1)

  • Filter: (org_key = 2)
  • Rows Removed by Filter: 914
28. 115.132 115.132 ↓ 0.0 0 538

Index Scan using options_filter_pkey on options o_1 (cost=0.42..8.44 rows=1 width=10) (actual time=0.214..0.214 rows=0 loops=538)

  • Index Cond: ((org_key = 2) AND (option_key = na_1.option_key))
  • Filter: stock_active
  • Rows Removed by Filter: 1
29. 0.031 0.031 ↑ 1.4 8 1

Index Scan using sku_option_idx on skus sk_1 (cost=0.42..31.22 rows=11 width=311) (actual time=0.027..0.031 rows=8 loops=1)

  • Index Cond: ((org_key = 2) AND (option_key = o_1.option_key))
30. 174,279.840 214,310.880 ↓ 213,782.0 213,782 1,680

GroupAggregate (cost=168,126.14..168,126.17 rows=1 width=20) (actual time=1.881..127.566 rows=213,782 loops=1,680)

  • Group Key: i.org_key, i.inbound_key, pp.store_key, i.sku_key
31. 37,278.350 40,031.040 ↓ 217,642.0 217,642 1,680

Sort (cost=168,126.14..168,126.14 rows=1 width=24) (actual time=1.879..23.828 rows=217,642 loops=1,680)

  • Sort Key: i.inbound_key, pp.store_key, i.sku_key
  • Sort Method: quicksort Memory: 67,561kB
32. 203.134 2,752.690 ↓ 550,200.0 550,200 1

Merge Join (cost=167,074.94..168,126.13 rows=1 width=24) (actual time=2,505.973..2,752.690 rows=550,200 loops=1)

  • Merge Cond: ((i.inbound_key = pp.inbound_key) AND (i.option_key = pp.option_key) AND (i.prepack_key = pp.prepack_key))
33. 7.158 9.998 ↓ 1.2 6,787 1

Sort (cost=702.96..716.77 rows=5,523 width=35) (actual time=9.213..9.998 rows=6,787 loops=1)

  • Sort Key: i.inbound_key, i.option_key, i.prepack_key
  • Sort Method: quicksort Memory: 820kB
34. 2.840 2.840 ↓ 1.2 6,794 1

Seq Scan on inbounds i (cost=0.00..359.68 rows=5,523 width=35) (actual time=0.086..2.840 rows=6,794 loops=1)

  • Filter: (org_key = 2)
  • Rows Removed by Filter: 7,541
35. 99.655 2,539.558 ↓ 5.5 552,715 1

Sort (cost=166,371.61..166,620.74 rows=99,650 width=36) (actual time=2,496.748..2,539.558 rows=552,715 loops=1)

  • Sort Key: pp.inbound_key, pp.option_key, pp.prepack_key
  • Sort Method: quicksort Memory: 11,669kB
36. 2,439.903 2,439.903 ↓ 1.1 110,040 1

Seq Scan on inbounds_prepacks pp (cost=0.00..158,098.38 rows=99,650 width=36) (actual time=2,244.833..2,439.903 rows=110,040 loops=1)

  • Filter: ((org_key = 2) AND (date_key = (now())::date))
  • Rows Removed by Filter: 5,426,430
37. 26.880 26.880 ↑ 1.0 1 1,680

Index Only Scan using stores_pkey on stores st (cost=0.28..2.30 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1,680)

  • Index Cond: ((org_key = 2) AND (store_key = p_1.store_key))
  • Heap Fetches: 0
38. 0.180 11,355.666 ↓ 210.0 420 1

Subquery Scan on *SELECT* 3 (cost=0.98..52,794.36 rows=2 width=85) (actual time=5,257.293..11,355.666 rows=420 loops=1)

39. 0.157 11,355.486 ↓ 210.0 420 1

Nested Loop (cost=0.98..52,794.33 rows=2 width=57) (actual time=5,257.289..11,355.486 rows=420 loops=1)

40. 0.188 4.184 ↑ 1.0 1 1

Nested Loop (cost=0.42..90.79 rows=1 width=44) (actual time=0.944..4.184 rows=1 loops=1)

41. 0.230 0.230 ↓ 76.9 538 1

CTE Scan on not_allocated na_2 (cost=0.00..31.70 rows=7 width=34) (actual time=0.004..0.230 rows=538 loops=1)

  • Filter: (org_key = 2)
  • Rows Removed by Filter: 914
42. 3.766 3.766 ↓ 0.0 0 538

Index Scan using options_filter_pkey on options o_2 (cost=0.42..8.44 rows=1 width=10) (actual time=0.007..0.007 rows=0 loops=538)

  • Index Cond: ((org_key = 2) AND (option_key = na_2.option_key))
  • Filter: stock_active
  • Rows Removed by Filter: 1
43. 11,351.145 11,351.145 ↓ 6.0 420 1

Index Scan using inbounds_prepacks_pkey on inbounds_prepacks p_2 (cost=0.56..52,702.84 rows=70 width=32) (actual time=5,256.336..11,351.145 rows=420 loops=1)

  • Index Cond: ((org_key = 2) AND (option_key = o_2.option_key) AND (date_key = (now())::date))
Planning time : 27.361 ms