explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aAD8

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 5.097 ↑ 65.0 1 1

Bitmap Heap Scan on "Participant" p (cost=1,031.15..1,063.74 rows=65 width=182) (actual time=5.094..5.097 rows=1 loops=1)

  • Recheck Cond: ("user" = '0c855e6a-a4fc-42f3-bdb5-911fb90152df'::uuid)
  • Filter: (("deletedAt" IS NULL) AND ((hashed SubPlan 8) OR (hashed SubPlan 9)))
  • Rows Removed by Filter: 90
  • Heap Blocks: exact=11
2.          

CTE participants_ids

3. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on "Participant" p_1 (cost=4.99..37.12 rows=91 width=16) (never executed)

  • Recheck Cond: ("user" = '0c855e6a-a4fc-42f3-bdb5-911fb90152df'::uuid)
4. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on "Participant_user_idx" (cost=0.00..4.96 rows=91 width=0) (never executed)

  • Index Cond: ("user" = '0c855e6a-a4fc-42f3-bdb5-911fb90152df'::uuid)
5.          

CTE show_participants_ids

6. 0.001 0.008 ↓ 0.0 0 1

Hash Semi Join (cost=3.24..11.26 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1)

  • Hash Cond: (sp.participant = participants_ids.id)
7. 0.007 0.007 ↓ 0.0 0 1

Index Only Scan using "ShowParticipant_show_participant_key" on "ShowParticipant" sp (cost=0.28..8.30 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (show = '31c3cf98-3238-47f7-8131-28784fb91ee4'::uuid)
  • Heap Fetches: 0
8. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.82..1.82 rows=91 width=16) (never executed)

9. 0.000 0.000 ↓ 0.0 0

CTE Scan on participants_ids (cost=0.00..1.82 rows=91 width=16) (never executed)

10.          

CTE show_parent

11. 0.008 0.008 ↑ 1.0 1 1

Index Scan using "Show_pkey" on "Show" s (cost=0.29..8.30 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (id = '31c3cf98-3238-47f7-8131-28784fb91ee4'::uuid)
12.          

CTE hierarchy_nodes

13. 4.869 4.880 ↑ 6,202.0 3 1

Seq Scan on "FlatHierarchyM" fh (cost=0.04..537.16 rows=18,606 width=16) (actual time=1.067..4.880 rows=3 loops=1)

  • Filter: ((hashed SubPlan 4) OR (hashed SubPlan 5))
  • Rows Removed by Filter: 24805
14.          

SubPlan (forSeq Scan)

15. 0.011 0.011 ↑ 1.0 1 1

CTE Scan on show_parent (cost=0.00..0.02 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=1)

16. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on show_parent show_parent_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1)

17.          

CTE hierarchy_participants

18. 0.061 5.016 ↑ 34.0 3 1

Hash Join (cost=423.13..430.00 rows=102 width=16) (actual time=4.960..5.016 rows=3 loops=1)

  • Hash Cond: (hp.node = hierarchy_nodes.parent)
19. 0.051 0.051 ↑ 1.0 205 1

Seq Scan on "HierarchyParticipant" hp (cost=0.00..4.05 rows=205 width=32) (actual time=0.005..0.051 rows=205 loops=1)

20. 0.002 4.904 ↑ 66.7 3 1

Hash (cost=420.63..420.63 rows=200 width=16) (actual time=4.904..4.904 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.007 4.902 ↑ 66.7 3 1

HashAggregate (cost=418.63..420.63 rows=200 width=16) (actual time=4.900..4.902 rows=3 loops=1)

  • Group Key: hierarchy_nodes.parent
22. 4.895 4.895 ↑ 6,202.0 3 1

CTE Scan on hierarchy_nodes (cost=0.00..372.12 rows=18,606 width=16) (actual time=1.069..4.895 rows=3 loops=1)

23. 0.020 0.020 ↑ 1.0 91 1

Bitmap Index Scan on "Participant_user_idx" (cost=0.00..4.96 rows=91 width=0) (actual time=0.020..0.020 rows=91 loops=1)

  • Index Cond: ("user" = '0c855e6a-a4fc-42f3-bdb5-911fb90152df'::uuid)
24.          

SubPlan (forBitmap Heap Scan)

25. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on show_participants_ids (cost=0.00..0.02 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1)

26. 5.020 5.020 ↑ 34.0 3 1

CTE Scan on hierarchy_participants (cost=0.00..2.04 rows=102 width=16) (actual time=4.961..5.020 rows=3 loops=1)