explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4sSs

Settings
# exclusive inclusive rows x rows loops node
1. 0.069 42.682 ↑ 33.8 30 1

Subquery Scan on y (cost=18,322.02..18,420.89 rows=1,014 width=112) (actual time=42.158..42.682 rows=30 loops=1)

  • Filter: (y.row_number <= 5)
  • Rows Removed by Filter: 1115
  • Buffers: shared hit=10858
2.          

CTE inventory

3. 0.794 41.136 ↑ 2.7 1,145 1

Hash Join (cost=13,034.72..18,085.19 rows=3,042 width=81) (actual time=35.331..41.136 rows=1,145 loops=1)

  • Hash Cond: (pv.product_id = p.id)
  • Buffers: shared hit=10858
4. 0.317 36.360 ↑ 1.1 1,145 1

Hash Join (cost=12,948.62..13,052.41 rows=1,305 width=53) (actual time=34.767..36.360 rows=1,145 loops=1)

  • Hash Cond: (pv.id = i.product_variant_id)
  • Buffers: shared hit=9662
5. 0.311 1.875 ↑ 1.0 1,564 1

Hash Join (cost=64.35..164.02 rows=1,565 width=53) (actual time=0.586..1.875 rows=1,564 loops=1)

  • Hash Cond: (pc.category_id = c.id)
  • Buffers: shared hit=56
6. 0.678 1.559 ↑ 1.0 1,564 1

Hash Join (cost=63.21..155.87 rows=1,565 width=48) (actual time=0.578..1.559 rows=1,564 loops=1)

  • Hash Cond: (pc.product_id = pv.product_id)
  • Buffers: shared hit=55
7. 0.312 0.312 ↑ 1.0 3,615 1

Seq Scan on product_category pc (cost=0.00..63.37 rows=3,637 width=8) (actual time=0.004..0.312 rows=3,615 loops=1)

  • Buffers: shared hit=27
8. 0.289 0.569 ↓ 1.0 1,567 1

Hash (cost=43.65..43.65 rows=1,565 width=40) (actual time=0.569..0.569 rows=1,567 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 130kB
  • Buffers: shared hit=28
9. 0.280 0.280 ↓ 1.0 1,567 1

Seq Scan on product_variant pv (cost=0.00..43.65 rows=1,565 width=40) (actual time=0.006..0.280 rows=1,567 loops=1)

  • Buffers: shared hit=28
10. 0.003 0.005 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=13) (actual time=0.005..0.005 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
11. 0.002 0.002 ↑ 1.0 6 1

Seq Scan on category c (cost=0.00..1.06 rows=6 width=13) (actual time=0.001..0.002 rows=6 loops=1)

  • Buffers: shared hit=1
12. 0.164 34.168 ↑ 1.1 1,146 1

Hash (cost=12,867.96..12,867.96 rows=1,305 width=4) (actual time=34.168..34.168 rows=1,146 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 57kB
  • Buffers: shared hit=9606
13. 0.570 34.004 ↑ 1.1 1,146 1

Finalize HashAggregate (cost=12,841.86..12,854.91 rows=1,305 width=4) (actual time=33.886..34.004 rows=1,146 loops=1)

  • Group Key: i.product_variant_id
  • Buffers: shared hit=9606
14. 4.572 33.434 ↓ 1.2 3,137 1

Gather (cost=12,561.28..12,835.33 rows=2,610 width=4) (actual time=32.759..33.434 rows=3,137 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=9606
15. 5.371 28.862 ↑ 1.2 1,046 3 / 3

Partial HashAggregate (cost=11,561.28..11,574.33 rows=1,305 width=4) (actual time=28.741..28.862 rows=1,046 loops=3)

  • Group Key: i.product_variant_id
  • Buffers: shared hit=9606
16. 6.699 23.491 ↓ 8.3 37,452 3 / 3

Hash Join (cost=1,885.37..11,549.98 rows=4,520 width=4) (actual time=1.819..23.491 rows=37,452 loops=3)

  • Hash Cond: (i.poc_id = p_1.id)
  • Buffers: shared hit=9606
17. 15.001 15.001 ↑ 1.2 44,553 3 / 3

Parallel Seq Scan on inventory_item i (cost=0.00..9,528.35 rows=51,887 width=8) (actual time=0.015..15.001 rows=44,553 loops=3)

  • Filter: ((available_date IS NOT NULL) AND (price > '0'::double precision))
  • Rows Removed by Filter: 36232
  • Buffers: shared hit=8270
18. 0.140 1.791 ↑ 1.0 912 3 / 3

Hash (cost=1,873.97..1,873.97 rows=912 width=4) (actual time=1.791..1.791 rows=912 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
  • Buffers: shared hit=1336
19. 1.651 1.651 ↑ 1.0 912 3 / 3

Index Scan using ix_poc_status on poc p_1 (cost=0.29..1,873.97 rows=912 width=4) (actual time=0.046..1.651 rows=912 loops=3)

  • Index Cond: (status = ANY ('{OPERATIONAL,ASSISTED_OPERATIONAL}'::poc_status[]))
  • Buffers: shared hit=1336
20. 0.216 0.547 ↓ 1.0 1,571 1

Hash (cost=66.60..66.60 rows=1,560 width=12) (actual time=0.547..0.547 rows=1,571 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 80kB
  • Buffers: shared hit=51
21. 0.331 0.331 ↓ 1.0 1,571 1

Seq Scan on product p (cost=0.00..66.60 rows=1,560 width=12) (actual time=0.006..0.331 rows=1,571 loops=1)

  • Buffers: shared hit=51
22.          

SubPlan (for Hash Join)

23. 3.435 3.435 ↓ 0.0 0 1,145

Seq Scan on brand b (cost=0.00..1.62 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1,145)

  • Filter: (id = p.brand_id)
  • Rows Removed by Filter: 51
  • Buffers: shared hit=1145
24. 0.409 42.613 ↑ 2.7 1,145 1

WindowAgg (cost=236.83..297.67 rows=3,042 width=112) (actual time=42.157..42.613 rows=1,145 loops=1)

  • Buffers: shared hit=10858
25. 0.583 42.204 ↑ 2.7 1,145 1

Sort (cost=236.83..244.44 rows=3,042 width=104) (actual time=42.151..42.204 rows=1,145 loops=1)

  • Sort Key: inventory.category, inventory.showcase_order
  • Sort Method: quicksort Memory: 176kB
  • Buffers: shared hit=10858
26. 41.621 41.621 ↑ 2.7 1,145 1

CTE Scan on inventory (cost=0.00..60.84 rows=3,042 width=104) (actual time=35.334..41.621 rows=1,145 loops=1)

  • Buffers: shared hit=10858
Planning time : 0.854 ms
Execution time : 43.016 ms