explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tkgI

Settings
# exclusive inclusive rows x rows loops node
1. 0.046 42.915 ↑ 65.0 1 1

Bitmap Heap Scan on "Participant" p (cost=100,658.42..100,691.01 rows=65 width=182) (actual time=42.911..42.915 rows=1 loops=1)

  • Recheck Cond: ("user" = '0c855e6a-a4fc-42f3-bdb5-911fb90152df'::uuid)
  • Filter: (("deletedAt" IS NULL) AND ((hashed SubPlan 9) OR (hashed SubPlan 10)))
  • 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.005 ↓ 0.0 0 1

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

  • Hash Cond: (sp.participant = participants_ids.id)
7. 0.004 0.004 ↓ 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.004..0.004 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.007 0.007 ↑ 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.007 rows=1 loops=1)

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

CTE hierarchy_nodes

13. 4.603 42.772 ↑ 229,042.7 3 1

Subquery Scan on fh (cost=53,056.71..85,122.69 rows=687,128 width=16) (actual time=9.925..42.772 rows=3 loops=1)

  • Filter: ((hashed SubPlan 4) OR (hashed SubPlan 5))
  • Rows Removed by Filter: 24805
14. 38.160 38.160 ↑ 36.9 24,808 1

CTE Scan on computed (cost=53,056.66..71,380.08 rows=916,171 width=32) (actual time=3.400..38.160 rows=24,808 loops=1)

15.          

CTE computed

16. 13.596 31.239 ↑ 36.9 24,808 1

Recursive Union (cost=343.10..53,056.66 rows=916,171 width=48) (actual time=3.398..31.239 rows=24,808 loops=1)

17. 3.655 7.917 ↓ 1.8 16,427 1

Hash Right Join (cost=343.10..687.64 rows=9,071 width=48) (actual time=3.395..7.917 rows=16,427 loops=1)

  • Hash Cond: (hh.parent = h.id)
18. 0.898 0.898 ↑ 1.0 9,071 1

Seq Scan on "HierarchyNode" hh (cost=0.00..229.71 rows=9,071 width=32) (actual time=0.006..0.898 rows=9,071 loops=1)

19. 1.717 3.364 ↑ 1.0 9,071 1

Hash (cost=229.71..229.71 rows=9,071 width=32) (actual time=3.364..3.364 rows=9,071 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 680kB
20. 1.647 1.647 ↑ 1.0 9,071 1

Seq Scan on "HierarchyNode" h (cost=0.00..229.71 rows=9,071 width=32) (actual time=0.007..1.647 rows=9,071 loops=1)

21. 5.362 9.726 ↑ 17.3 5,246 3

Hash Join (cost=343.10..3,404.56 rows=90,710 width=48) (actual time=1.016..3.242 rows=5,246 loops=3)

  • Hash Cond: (c.acc = h_1.id)
22. 1.878 1.878 ↑ 11.0 8,269 3

WorkTable Scan on computed c (cost=0.00..1,814.20 rows=90,710 width=32) (actual time=0.000..0.626 rows=8,269 loops=3)

23. 1.319 2.486 ↑ 1.0 9,071 1

Hash (cost=229.71..229.71 rows=9,071 width=32) (actual time=2.486..2.486 rows=9,071 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 680kB
24. 1.167 1.167 ↑ 1.0 9,071 1

Seq Scan on "HierarchyNode" h_1 (cost=0.00..229.71 rows=9,071 width=32) (actual time=0.012..1.167 rows=9,071 loops=1)

25.          

SubPlan (forSubquery Scan)

26. 0.009 0.009 ↑ 1.0 1 1

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

27. 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)

28.          

CTE hierarchy_participants

29. 0.036 42.846 ↑ 34.0 3 1

Hash Join (cost=15,464.88..15,471.75 rows=102 width=16) (actual time=42.822..42.846 rows=3 loops=1)

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

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

31. 0.002 42.787 ↑ 66.7 3 1

Hash (cost=15,462.38..15,462.38 rows=200 width=16) (actual time=42.787..42.787 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.008 42.785 ↑ 66.7 3 1

HashAggregate (cost=15,460.38..15,462.38 rows=200 width=16) (actual time=42.784..42.785 rows=3 loops=1)

  • Group Key: hierarchy_nodes.parent
33. 42.777 42.777 ↑ 229,042.7 3 1

CTE Scan on hierarchy_nodes (cost=0.00..13,742.56 rows=687,128 width=16) (actual time=9.926..42.777 rows=3 loops=1)

34. 0.017 0.017 ↑ 1.0 91 1

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

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

SubPlan (forBitmap Heap Scan)

36. 0.005 0.005 ↓ 0.0 0 1

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

37. 42.847 42.847 ↑ 34.0 3 1

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