explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W1jT

Settings
# exclusive inclusive rows x rows loops node
1. 0.190 10,240.745 ↑ 5.7 273 1

Sort (cost=502,113.77..502,117.69 rows=1,569 width=104) (actual time=10,240.730..10,240.745 rows=273 loops=1)

  • Sort Key: f.dc_stock DESC
  • Sort Method: quicksort Memory: 57kB
2.          

CTE f

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

4. 3.258 10,240.555 ↑ 5.7 273 1

Hash Right Join (cost=498,172.73..502,030.48 rows=1,569 width=104) (actual time=10,131.764..10,240.555 rows=273 loops=1)

  • Hash Cond: ((sk.org_key = f.org_key) AND (sk.option_key = f.option_key))
5. 11.446 244.417 ↓ 43.4 23,693 1

Subquery Scan on sk (cost=42,437.03..46,260.53 rows=546 width=353) (actual time=130.110..244.417 rows=23,693 loops=1)

  • Filter: (sk.rn = 1)
  • Rows Removed by Filter: 85543
6. 74.530 232.971 ↑ 1.0 109,236 1

WindowAgg (cost=42,437.03..44,895.00 rows=109,243 width=438) (actual time=130.109..232.971 rows=109,236 loops=1)

7. 126.508 158.441 ↑ 1.0 109,236 1

Sort (cost=42,437.03..42,710.14 rows=109,243 width=357) (actual time=130.095..158.441 rows=109,236 loops=1)

  • Sort Key: skus.org_key, skus.option_key, skus.sku_key
  • Sort Method: external merge Disk: 39376kB
8. 31.933 31.933 ↑ 1.0 109,236 1

Seq Scan on skus (cost=0.00..15,371.43 rows=109,243 width=357) (actual time=0.029..31.933 rows=109,236 loops=1)

9. 0.075 9,992.880 ↑ 5.7 273 1

Hash (cost=455,712.17..455,712.17 rows=1,569 width=72) (actual time=9,992.880..9,992.880 rows=273 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 32kB
10. 0.044 9,992.805 ↑ 5.7 273 1

Subquery Scan on f (cost=455,039.78..455,712.17 rows=1,569 width=72) (actual time=9,992.202..9,992.805 rows=273 loops=1)

11. 0.527 9,992.761 ↑ 5.7 273 1

GroupAggregate (cost=455,039.78..455,696.48 rows=1,569 width=72) (actual time=9,992.200..9,992.761 rows=273 loops=1)

  • Group Key: sk_1.org_key, sk_1.option_key
12. 0.298 9,992.234 ↑ 56.6 552 1

Sort (cost=455,039.78..455,117.94 rows=31,266 width=48) (actual time=9,992.190..9,992.234 rows=552 loops=1)

  • Sort Key: sk_1.option_key
  • Sort Method: quicksort Memory: 68kB
13. 4.029 9,991.936 ↑ 56.6 552 1

Merge Join (cost=451,016.37..452,705.41 rows=31,266 width=48) (actual time=8,002.757..9,991.936 rows=552 loops=1)

  • Merge Cond: (dc.sku_key = sk_1.sku_key)
14. 1.329 13.435 ↓ 4.6 3,696 1

Sort (cost=5,058.51..5,060.50 rows=797 width=14) (actual time=12.871..13.435 rows=3,696 loops=1)

  • Sort Key: dc.sku_key
  • Sort Method: quicksort Memory: 270kB
15. 0.564 12.106 ↓ 4.6 3,696 1

Subquery Scan on dc (cost=4,982.23..5,020.10 rows=797 width=14) (actual time=9.272..12.106 rows=3,696 loops=1)

16. 5.474 11.542 ↓ 4.6 3,696 1

HashAggregate (cost=4,982.23..5,012.13 rows=797 width=14) (actual time=9.271..11.542 rows=3,696 loops=1)

  • Group Key: sc.org_key, sc.sku_key
  • Filter: (sum(sc.technical) > 10)
  • Rows Removed by Filter: 9118
17. 2.166 6.068 ↓ 5.4 12,814 1

Nested Loop (cost=0.57..4,958.31 rows=2,392 width=10) (actual time=0.023..6.068 rows=12,814 loops=1)

18. 0.049 0.049 ↑ 1.0 1 1

Index Scan using stores_pkey on stores st (cost=0.14..33.71 rows=1 width=4) (actual time=0.010..0.049 rows=1 loops=1)

  • Index Cond: (org_key = 1)
  • Filter: (store_status = 'DC'::text)
  • Rows Removed by Filter: 152
19. 3.853 3.853 ↓ 3.6 12,814 1

Index Scan using stock_current_pkey on stock_current sc (cost=0.42..4,889.09 rows=3,552 width=12) (actual time=0.011..3.853 rows=12,814 loops=1)

  • Index Cond: ((org_key = 1) AND (store_key = st.store_key))
20. 5.613 9,974.472 ↓ 3.3 26,280 1

Materialize (cost=445,957.86..447,193.54 rows=7,846 width=44) (actual time=7,984.571..9,974.472 rows=26,280 loops=1)

21. 16.987 9,968.859 ↓ 3.3 26,280 1

Nested Loop (cost=445,957.86..447,173.92 rows=7,846 width=44) (actual time=7,984.569..9,968.859 rows=26,280 loops=1)

  • Join Filter: CASE WHEN (array_length(f_1.groups, 1) IS NULL) THEN true ELSE (sk_1.group_key = ANY (f_1.groups)) END
22. 1,475.581 9,951.872 ↓ 1.7 26,280 1

GroupAggregate (cost=445,957.86..446,467.82 rows=15,691 width=46) (actual time=7,984.560..9,951.872 rows=26,280 loops=1)

  • Group Key: sk_1.org_key, sk_1.sku_key
23. 2,307.578 8,476.291 ↓ 195.7 3,070,596 1

Sort (cost=445,957.86..445,997.09 rows=15,691 width=26) (actual time=7,984.446..8,476.291 rows=3,070,596 loops=1)

  • Sort Key: sk_1.sku_key
  • Sort Method: external merge Disk: 111128kB
24. 2,138.262 6,168.713 ↓ 195.7 3,070,596 1

Hash Join (cost=365,507.97..444,864.38 rows=15,691 width=26) (actual time=1,754.518..6,168.713 rows=3,070,596 loops=1)

  • Hash Cond: (p.option_key = sk_1.option_key)
  • Join Filter: (p.price < (sk_1.original_price * 0.9))
  • Rows Removed by Join Filter: 670704
25. 208.073 3,961.383 ↓ 86.5 796,579 1

Merge Join (cost=347,856.90..425,292.75 rows=9,209 width=14) (actual time=1,685.326..3,961.383 rows=796,579 loops=1)

  • Merge Cond: (p.store_key = st_1.store_key)
26. 241.419 3,752.793 ↓ 85.9 899,354 1

Subquery Scan on p (cost=347,856.75..426,401.14 rows=10,473 width=14) (actual time=1,685.300..3,752.793 rows=899,354 loops=1)

  • Filter: (p.rn = 1)
  • Rows Removed by Filter: 1195163
27. 1,368.409 3,511.374 ↑ 1.0 2,094,517 1

WindowAgg (cost=347,856.75..400,219.68 rows=2,094,517 width=26) (actual time=1,685.298..3,511.374 rows=2,094,517 loops=1)

28. 1,896.217 2,142.965 ↑ 1.0 2,094,517 1

Sort (cost=347,856.75..353,093.04 rows=2,094,517 width=18) (actual time=1,685.285..2,142.965 rows=2,094,517 loops=1)

  • Sort Key: options_prices.store_key, options_prices.option_key, options_prices.date_min DESC
  • Sort Method: external merge Disk: 57992kB
29. 246.748 246.748 ↑ 1.0 2,094,517 1

Seq Scan on options_prices (cost=0.00..42,040.46 rows=2,094,517 width=18) (actual time=0.007..246.748 rows=2,094,517 loops=1)

  • Filter: (org_key = 1)
30. 0.517 0.517 ↑ 1.0 153 1

Index Only Scan using stores_pkey on stores st_1 (cost=0.14..10.82 rows=153 width=4) (actual time=0.021..0.517 rows=153 loops=1)

  • Index Cond: (org_key = 1)
  • Heap Fetches: 59
31. 25.929 69.068 ↑ 1.0 109,236 1

Hash (cost=15,644.54..15,644.54 rows=109,243 width=20) (actual time=69.068..69.068 rows=109,236 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3379kB
32. 43.139 43.139 ↑ 1.0 109,236 1

Seq Scan on skus sk_1 (cost=0.00..15,644.54 rows=109,243 width=20) (actual time=0.012..43.139 rows=109,236 loops=1)

  • Filter: (stock_active AND (org_key = 1))
33. 0.000 0.000 ↑ 1.0 1 26,280

CTE Scan on f f_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=26,280)

Planning time : 0.774 ms