explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lo4u

Settings
# exclusive inclusive rows x rows loops node
1. 0.067 49.717 ↑ 33.8 30 1

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

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

CTE inventory

3. 0.866 48.241 ↑ 2.7 1,145 1

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

  • Hash Cond: (pv.product_id = p.id)
  • Buffers: shared hit=10846
4. 0.333 43.394 ↑ 1.1 1,145 1

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

  • Hash Cond: (pv.id = i.product_variant_id)
  • Buffers: shared hit=9650
5. 0.293 1.838 ↑ 1.0 1,564 1

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

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

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

  • Hash Cond: (pc.product_id = pv.product_id)
  • Buffers: shared hit=55
7. 0.310 0.310 ↑ 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.310 rows=3,615 loops=1)

  • Buffers: shared hit=27
8. 0.292 0.585 ↓ 1.0 1,567 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 130kB
  • Buffers: shared hit=28
9. 0.293 0.293 ↓ 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.293 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.157 41.223 ↑ 1.1 1,146 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 57kB
  • Buffers: shared hit=9594
13. 0.687 41.066 ↑ 1.1 1,146 1

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

  • Group Key: i.product_variant_id
  • Buffers: shared hit=9594
14. 7.295 40.379 ↓ 1.2 3,120 1

Gather (cost=12,561.28..12,835.33 rows=2,610 width=4) (actual time=35.735..40.379 rows=3,120 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=9594
15. 6.377 33.084 ↑ 1.3 1,040 3 / 3

Partial HashAggregate (cost=11,561.28..11,574.33 rows=1,305 width=4) (actual time=32.933..33.084 rows=1,040 loops=3)

  • Group Key: i.product_variant_id
  • Buffers: shared hit=9594
16. 7.732 26.707 ↓ 8.3 37,447 3 / 3

Hash Join (cost=1,885.37..11,549.98 rows=4,520 width=4) (actual time=1.738..26.707 rows=37,447 loops=3)

  • Hash Cond: (i.poc_id = p_1.id)
  • Buffers: shared hit=9594
17. 17.269 17.269 ↑ 1.2 44,547 3 / 3

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
  • Buffers: shared hit=1324
19. 1.542 1.542 ↑ 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.048..1.542 rows=912 loops=3)

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

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

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

Seq Scan on product p (cost=0.00..66.60 rows=1,560 width=12) (actual time=0.007..0.329 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.348 49.650 ↑ 2.7 1,145 1

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

  • Buffers: shared hit=10846
25. 0.569 49.302 ↑ 2.7 1,145 1

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

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

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

  • Buffers: shared hit=10846
Planning time : 0.984 ms
Execution time : 50.004 ms