explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PHPs

Settings
# exclusive inclusive rows x rows loops node
1. 0.208 33.308 ↑ 20.8 30 1

Subquery Scan on y (cost=1,099.03..1,159.77 rows=623 width=96) (actual time=32.280..33.308 rows=30 loops=1)

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

CTE inventory

3. 0.522 30.869 ↑ 2.2 838 1

Hash Join (cost=877.44..960.09 rows=1,869 width=90) (actual time=28.525..30.869 rows=838 loops=1)

  • Hash Cond: (pv.product_id = p.id)
  • Buffers: shared hit=609
4. 0.466 29.060 ↑ 1.3 838 1

Hash Join (cost=833.71..901.66 rows=1,069 width=94) (actual time=27.229..29.060 rows=838 loops=1)

  • Hash Cond: (pc.category_id = c.id)
  • Buffers: shared hit=592
5. 0.889 28.582 ↑ 1.3 838 1

Hash Join (cost=832.57..885.82 rows=1,069 width=66) (actual time=27.207..28.582 rows=838 loops=1)

  • Hash Cond: (pc.product_id = pv.product_id)
  • Buffers: shared hit=591
6. 0.500 0.500 ↑ 1.0 2,077 1

Seq Scan on product_category pc (cost=0.00..34.77 rows=2,077 width=8) (actual time=0.004..0.500 rows=2,077 loops=1)

  • Buffers: shared hit=14
7. 0.362 27.193 ↑ 1.3 839 1

Hash (cost=819.21..819.21 rows=1,069 width=58) (actual time=27.193..27.193 rows=839 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 92kB
  • Buffers: shared hit=577
8. 0.592 26.831 ↑ 1.3 839 1

Hash Join (cost=783.13..819.21 rows=1,069 width=58) (actual time=25.979..26.831 rows=839 loops=1)

  • Hash Cond: (i.product_variant_id = pv.id)
  • Buffers: shared hit=577
9. 10.338 25.381 ↑ 1.3 839 1

HashAggregate (cost=736.49..747.18 rows=1,069 width=20) (actual time=25.104..25.381 rows=839 loops=1)

  • Group Key: i.product_variant_id
  • Buffers: shared hit=557
10. 9.191 15.043 ↓ 11.9 19,032 1

Nested Loop (cost=0.43..724.54 rows=1,593 width=12) (actual time=0.031..15.043 rows=19,032 loops=1)

  • Buffers: shared hit=557
11. 0.092 0.092 ↑ 1.0 240 1

Index Only Scan using test_poc on poc p_1 (cost=0.14..11.74 rows=240 width=4) (actual time=0.013..0.092 rows=240 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=2
12. 5.760 5.760 ↓ 3.4 79 240

Index Only Scan using test_inventory_item on inventory_item i (cost=0.29..2.74 rows=23 width=16) (actual time=0.003..0.024 rows=79 loops=240)

  • Index Cond: (poc_id = p_1.id)
  • Heap Fetches: 0
  • Buffers: shared hit=555
13. 0.412 0.858 ↑ 1.0 1,184 1

Hash (cost=31.84..31.84 rows=1,184 width=42) (actual time=0.858..0.858 rows=1,184 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 104kB
  • Buffers: shared hit=20
14. 0.446 0.446 ↑ 1.0 1,184 1

Seq Scan on product_variant pv (cost=0.00..31.84 rows=1,184 width=42) (actual time=0.002..0.446 rows=1,184 loops=1)

  • Buffers: shared hit=20
15. 0.004 0.012 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=36) (actual time=0.012..0.012 rows=6 loops=1)

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

Seq Scan on category c (cost=0.00..1.06 rows=6 width=36) (actual time=0.005..0.008 rows=6 loops=1)

  • Buffers: shared hit=1
17. 0.369 1.287 ↑ 1.0 1,188 1

Hash (cost=28.88..28.88 rows=1,188 width=8) (actual time=1.287..1.287 rows=1,188 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
  • Buffers: shared hit=17
18. 0.918 0.918 ↑ 1.0 1,188 1

Seq Scan on product p (cost=0.00..28.88 rows=1,188 width=8) (actual time=0.008..0.918 rows=1,188 loops=1)

  • Buffers: shared hit=17
19. 0.611 33.100 ↑ 2.2 838 1

WindowAgg (cost=138.94..176.32 rows=1,869 width=96) (actual time=32.275..33.100 rows=838 loops=1)

  • Buffers: shared hit=609
20. 0.937 32.489 ↑ 2.2 838 1

Sort (cost=138.94..143.61 rows=1,869 width=88) (actual time=32.270..32.489 rows=838 loops=1)

  • Sort Key: inventory.category, inventory.showcase_order
  • Sort Method: quicksort Memory: 143kB
  • Buffers: shared hit=609
21. 31.552 31.552 ↑ 2.2 838 1

CTE Scan on inventory (cost=0.00..37.38 rows=1,869 width=88) (actual time=28.530..31.552 rows=838 loops=1)

  • Buffers: shared hit=609
Planning time : 1.111 ms
Execution time : 33.816 ms