explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TxK

Settings
# exclusive inclusive rows x rows loops node
1. 0.104 90.880 ↑ 65.0 1 1

Bitmap Heap Scan on "Participant" p (cost=100,658.42..100,691.01 rows=65 width=182) (actual time=90.876..90.880 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.007 ↓ 0.0 0 1

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

  • Hash Cond: (sp.participant = participants_ids.id)
7. 0.006 0.006 ↓ 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.006..0.006 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.009 0.009 ↑ 1.0 1 1

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

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

CTE hierarchy_nodes

13. 11.432 90.602 ↑ 229,042.7 3 1

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

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

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

15.          

CTE computed

16. 23.232 62.742 ↑ 36.9 24,808 1

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

17. 7.865 16.149 ↓ 1.8 16,427 1

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

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

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

19. 3.171 6.003 ↑ 1.0 9,071 1

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

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

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

21. 11.587 23.361 ↑ 17.3 5,246 3

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

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

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

23. 3.073 5.996 ↑ 1.0 9,071 1

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

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

Seq Scan on "HierarchyNode" h_1 (cost=0.00..229.71 rows=9,071 width=32) (actual time=0.008..2.923 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.068 90.745 ↑ 34.0 3 1

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

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

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

31. 0.003 90.621 ↑ 66.7 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.011 90.618 ↑ 66.7 3 1

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

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

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

34. 0.019 0.019 ↑ 1.0 91 1

Bitmap Index Scan on "Participant_user_idx" (cost=0.00..4.96 rows=91 width=0) (actual time=0.019..0.019 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. 90.749 90.749 ↑ 34.0 3 1

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

Planning time : 0.695 ms
Execution time : 91.937 ms