explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YIQR

Settings
# exclusive inclusive rows x rows loops node
1. 0.090 1,149.747 ↑ 65.0 1 1

Bitmap Heap Scan on "Participant" p (cost=99,431.26..99,598.39 rows=65 width=186) (actual time=1,149.742..1,149.747 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=14
2.          

CTE participants_ids

3. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on "Participant" p_1 (cost=4.99..171.66 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.006 ↓ 0.0 0 1

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

  • Hash Cond: (sp.participant = participants_ids.id)
7. 0.005 0.005 ↓ 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.005..0.005 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.013 0.013 ↑ 1.0 1 1

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

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

CTE hierarchy_nodes

13. 97.458 1,149.396 ↑ 225,533.0 3 1

Subquery Scan on fh (cost=52,425.19..83,999.81 rows=676,599 width=16) (actual time=271.340..1,149.396 rows=3 loops=1)

  • Filter: ((hashed SubPlan 4) OR (hashed SubPlan 5))
  • Rows Removed by Filter: 24787
14. 1,051.921 1,051.921 ↑ 36.4 24,790 1

CTE Scan on computed (cost=52,425.15..70,467.79 rows=902,132 width=32) (actual time=76.948..1,051.921 rows=24,790 loops=1)

15.          

CTE computed

16. 188.207 800.841 ↑ 36.4 24,790 1

Recursive Union (cost=352.97..52,425.15 rows=902,132 width=48) (actual time=76.946..800.841 rows=24,790 loops=1)

17. 187.806 268.708 ↓ 1.8 16,415 1

Hash Right Join (cost=352.97..707.31 rows=8,932 width=48) (actual time=76.940..268.708 rows=16,415 loops=1)

  • Hash Cond: (hh.parent = h.id)
18. 4.057 4.057 ↓ 1.0 9,065 1

Seq Scan on "HierarchyNode" hh (cost=0.00..241.32 rows=8,932 width=32) (actual time=0.004..4.057 rows=9,065 loops=1)

19. 5.471 76.845 ↓ 1.0 9,065 1

Hash (cost=241.32..241.32 rows=8,932 width=32) (actual time=76.845..76.845 rows=9,065 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 680kB
20. 71.374 71.374 ↓ 1.0 9,065 1

Seq Scan on "HierarchyNode" h (cost=0.00..241.32 rows=8,932 width=32) (actual time=0.006..71.374 rows=9,065 loops=1)

21. 185.704 343.926 ↑ 17.0 5,242 3

Hash Join (cost=352.97..3,367.52 rows=89,320 width=48) (actual time=41.109..114.642 rows=5,242 loops=3)

  • Hash Cond: (c.acc = h_1.id)
22. 69.567 69.567 ↑ 10.8 8,263 3

WorkTable Scan on computed c (cost=0.00..1,786.40 rows=89,320 width=32) (actual time=0.001..23.189 rows=8,263 loops=3)

23. 83.820 88.655 ↓ 1.0 9,065 1

Hash (cost=241.32..241.32 rows=8,932 width=32) (actual time=88.655..88.655 rows=9,065 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 680kB
24. 4.835 4.835 ↓ 1.0 9,065 1

Seq Scan on "HierarchyNode" h_1 (cost=0.00..241.32 rows=8,932 width=32) (actual time=0.008..4.835 rows=9,065 loops=1)

25.          

SubPlan (forSubquery Scan)

26. 0.015 0.015 ↑ 1.0 1 1

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

27. 0.002 0.002 ↑ 1.0 1 1

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

28.          

CTE hierarchy_participants

29. 0.113 1,149.622 ↑ 25.0 3 1

Hash Join (cost=15,227.98..15,233.54 rows=75 width=16) (actual time=1,149.524..1,149.622 rows=3 loops=1)

  • Hash Cond: (hp.node = hierarchy_nodes.parent)
30. 0.085 0.085 ↓ 1.4 205 1

Seq Scan on "HierarchyParticipant" hp (cost=0.00..3.50 rows=150 width=32) (actual time=0.007..0.085 rows=205 loops=1)

31. 0.006 1,149.424 ↑ 66.7 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.014 1,149.418 ↑ 66.7 3 1

HashAggregate (cost=15,223.48..15,225.48 rows=200 width=16) (actual time=1,149.415..1,149.418 rows=3 loops=1)

  • Group Key: hierarchy_nodes.parent
33. 1,149.404 1,149.404 ↑ 225,533.0 3 1

CTE Scan on hierarchy_nodes (cost=0.00..13,531.98 rows=676,599 width=16) (actual time=271.342..1,149.404 rows=3 loops=1)

34. 0.021 0.021 ↑ 1.0 91 1

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

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

SubPlan (forBitmap Heap Scan)

36. 0.008 0.008 ↓ 0.0 0 1

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

37. 1,149.628 1,149.628 ↑ 25.0 3 1

CTE Scan on hierarchy_participants (cost=0.00..1.50 rows=75 width=16) (actual time=1,149.526..1,149.628 rows=3 loops=1)