explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gd5W

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 40,983.168 ↓ 19.0 19 1

Limit (cost=50,265.35..50,265.36 rows=1 width=140) (actual time=40,983.164..40,983.168 rows=19 loops=1)

2.          

CTE f

3. 0.002 0.002 ↑ 1.0 1 1

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

4. 0.023 40,983.164 ↓ 19.0 19 1

Sort (cost=50,265.34..50,265.35 rows=1 width=140) (actual time=40,983.162..40,983.164 rows=19 loops=1)

  • Sort Key: (sum(sc.technical)) DESC
  • Sort Method: quicksort Memory: 27kB
5. 3.102 40,983.141 ↓ 19.0 19 1

Merge Left Join (cost=46,804.18..50,265.33 rows=1 width=140) (actual time=40,936.421..40,983.141 rows=19 loops=1)

  • Merge Cond: (sk_1.option_key = sk.option_key)
  • Join Filter: (sk_1.org_key = sk.org_key)
6. 1.875 40,739.436 ↓ 19.0 19 1

GroupAggregate (cost=13,418.40..13,418.43 rows=1 width=16) (actual time=40,736.746..40,739.436 rows=19 loops=1)

  • Group Key: sk_1.org_key, sk_1.option_key
  • Filter: (sum(sc.technical) > 100)
  • Rows Removed by Filter: 79
7. 7.277 40,737.561 ↓ 11,573.0 11,573 1

Sort (cost=13,418.40..13,418.40 rows=1 width=12) (actual time=40,736.653..40,737.561 rows=11,573 loops=1)

  • Sort Key: sk_1.option_key
  • Sort Method: quicksort Memory: 927kB
8. 11.939 40,730.284 ↓ 11,573.0 11,573 1

Nested Loop (cost=1,927.77..13,418.39 rows=1 width=12) (actual time=8,492.547..40,730.284 rows=11,573 loops=1)

9. 73.003 98.297 ↓ 2,968.0 2,968 1

Nested Loop (cost=1,927.35..9,354.98 rows=1 width=12) (actual time=4.419..98.297 rows=2,968 loops=1)

  • Join Filter: CASE WHEN (array_length(f.groups, 1) IS NULL) THEN true ELSE (sk_1.group_key = ANY (f.groups)) END
  • Rows Removed by Join Filter: 81836
10. 0.000 25.294 ↓ 84,804.0 84,804 1

Gather (cost=1,927.35..9,354.93 rows=1 width=14) (actual time=4.409..25.294 rows=84,804 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 11.680 34.802 ↓ 28,268.0 28,268 3

Hash Left Join (cost=927.35..8,354.83 rows=1 width=14) (actual time=9.704..34.802 rows=28,268 loops=3)

  • Hash Cond: ((sk_1.org_key = ss.org_key) AND (sk_1.option_key = ss.option_key))
  • Filter: (ss.settings IS NULL)
  • Rows Removed by Filter: 7192
12. 13.531 13.531 ↑ 1.3 35,460 3

Parallel Seq Scan on skus sk_1 (cost=0.00..6,991.23 rows=44,339 width=14) (actual time=0.006..13.531 rows=35,460 loops=3)

  • Filter: (stock_active AND (org_key = 1))
13. 4.762 9.591 ↓ 1.0 4,963 3

Hash (cost=852.98..852.98 rows=4,958 width=210) (actual time=9.590..9.591 rows=4,963 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 1252kB
14. 4.829 4.829 ↓ 1.0 4,963 3

Seq Scan on stock_settings_options ss (cost=0.00..852.98 rows=4,958 width=210) (actual time=0.010..4.829 rows=4,963 loops=3)

  • Filter: (org_key = 1)
15. 0.000 0.000 ↑ 1.0 1 84,804

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

16. 40,620.048 40,620.048 ↑ 5.8 4 2,968

Index Scan using stock_current_pkey on stock_current sc (cost=0.42..4,063.18 rows=23 width=10) (actual time=11.299..13.686 rows=4 loops=2,968)

  • Index Cond: ((org_key = 1) AND (sku_key = sk_1.sku_key))
17. 8.239 240.603 ↓ 44.6 23,702 1

Materialize (cost=33,385.78..36,845.54 rows=532 width=340) (actual time=138.808..240.603 rows=23,702 loops=1)

18. 9.600 232.364 ↓ 44.6 23,702 1

Subquery Scan on sk (cost=33,385.78..36,844.21 rows=532 width=340) (actual time=138.803..232.364 rows=23,702 loops=1)

  • Filter: (sk.rn = 1)
  • Rows Removed by Filter: 78936
19. 55.719 222.764 ↑ 1.0 102,638 1

WindowAgg (cost=33,385.78..35,514.04 rows=106,413 width=417) (actual time=138.801..222.764 rows=102,638 loops=1)

20. 124.469 167.045 ↑ 1.0 102,638 1

Sort (cost=33,385.78..33,651.82 rows=106,413 width=340) (actual time=138.790..167.045 rows=102,638 loops=1)

  • Sort Key: skus.option_key
  • Sort Method: external merge Disk: 36504kB
21. 42.576 42.576 ↑ 1.0 106,380 1

Seq Scan on skus (cost=0.00..7,767.16 rows=106,413 width=340) (actual time=0.012..42.576 rows=106,380 loops=1)

  • Filter: (org_key = 1)
Planning time : 0.617 ms