explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nIH8

Settings
# exclusive inclusive rows x rows loops node
1. 4.692 47.388 ↑ 256.1 176 1

HashAggregate (cost=92,493.29..92,944.11 rows=45,082 width=12) (actual time=47.137..47.388 rows=176 loops=1)

  • Group Key: view.account_id, message.channel_id
2. 8.088 42.696 ↑ 3.7 12,138 1

Nested Loop (cost=1,767.53..92,155.18 rows=45,082 width=12) (actual time=7.009..42.696 rows=12,138 loops=1)

3. 2.895 14.805 ↑ 4.5 6,601 1

Nested Loop (cost=1,767.10..74,835.82 rows=29,449 width=8) (actual time=7.002..14.805 rows=6,601 loops=1)

4. 0.036 7.010 ↑ 1.0 50 1

HashAggregate (cost=1,766.67..1,767.17 rows=50 width=4) (actual time=6.992..7.010 rows=50 loops=1)

  • Group Key: channel.id
5. 0.027 6.974 ↑ 1.0 50 1

Limit (cost=1,765.05..1,766.05 rows=50 width=4) (actual time=6.935..6.974 rows=50 loops=1)

6. 0.041 6.947 ↑ 1.3 50 1

HashAggregate (cost=1,765.05..1,765.72 rows=67 width=4) (actual time=6.934..6.947 rows=50 loops=1)

  • Group Key: channel.id
7. 0.025 6.906 ↑ 1.0 66 1

Append (cost=437.25..1,764.88 rows=67 width=4) (actual time=6.672..6.906 rows=66 loops=1)

8. 0.028 6.707 ↑ 1.0 66 1

Hash Right Join (cost=437.25..1,750.96 rows=66 width=4) (actual time=6.672..6.707 rows=66 loops=1)

  • Hash Cond: (o.id = channel.org_id)
  • Filter: ((channel.org_id IS NULL) OR (o.id IS NOT NULL))
9. 0.037 0.258 ↑ 118.0 1 1

Seq Scan on orgs o (cost=0.28..1,309.95 rows=118 width=4) (actual time=0.245..0.258 rows=1 loops=1)

  • Filter: ($2 OR (alternatives: SubPlan 4 or hashed SubPlan 5))
  • Rows Removed by Filter: 156
10.          

CTE is_global

11. 0.210 0.210 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=1) (actual time=0.209..0.210 rows=1 loops=1)

12.          

Initplan (for Seq Scan)

13. 0.212 0.212 ↑ 1.0 1 1

CTE Scan on is_global (cost=0.00..0.02 rows=1 width=1) (actual time=0.211..0.212 rows=1 loops=1)

14.          

SubPlan (for Seq Scan)

15. 0.000 0.000 ↓ 0.0 0

Index Scan using org_accounts_account_id_fk_i on org_accounts oa (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: (account_id = 1,004)
  • Filter: ((NOT deactivated) AND (org_id = o.id))
16. 0.009 0.009 ↑ 1.0 1 1

Index Scan using org_accounts_account_id_fk_i on org_accounts oa_1 (cost=0.28..8.30 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (account_id = 1,004)
  • Filter: (NOT deactivated)
17. 0.021 6.421 ↑ 1.0 66 1

Hash (cost=436.15..436.15 rows=66 width=8) (actual time=6.420..6.421 rows=66 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
18. 3.194 6.400 ↑ 1.0 66 1

Hash Join (cost=157.31..436.15 rows=66 width=8) (actual time=0.234..6.400 rows=66 loops=1)

  • Hash Cond: (channel.id = participant.channel_id)
19. 3.096 3.096 ↓ 1.0 13,622 1

Seq Scan on chat_channels channel (cost=0.00..243.10 rows=13,610 width=8) (actual time=0.006..3.096 rows=13,622 loops=1)

20. 0.023 0.110 ↑ 1.0 66 1

Hash (cost=156.48..156.48 rows=66 width=4) (actual time=0.110..0.110 rows=66 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
21. 0.074 0.087 ↑ 1.0 66 1

Bitmap Heap Scan on chat_channel_participants participant (cost=4.80..156.48 rows=66 width=4) (actual time=0.020..0.087 rows=66 loops=1)

  • Recheck Cond: (account_id = 1,004)
  • Heap Blocks: exact=50
22. 0.013 0.013 ↑ 1.0 66 1

Bitmap Index Scan on chat_channel_participants_account_id_fk_i (cost=0.00..4.78 rows=66 width=0) (actual time=0.013..0.013 rows=66 loops=1)

  • Index Cond: (account_id = 1,004)
23. 0.002 0.174 ↓ 0.0 0 1

Result (cost=9.25..13.26 rows=1 width=4) (actual time=0.173..0.174 rows=0 loops=1)

  • One-Time Filter: $0
24.          

Initplan (for Result)

25. 0.172 0.172 ↑ 1.0 1 1

Function Scan on is_global_account (cost=0.25..0.26 rows=1 width=1) (actual time=0.171..0.172 rows=1 loops=1)

26. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on chat_channels channel_1 (cost=9.25..13.26 rows=1 width=4) (never executed)

  • Recheck Cond: ((call_id IS NOT NULL) AND (org_id IS NULL))
27. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=8.99..8.99 rows=1 width=0) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on chat_channels_call_id_fk_i (cost=0.00..4.31 rows=3 width=0) (never executed)

  • Index Cond: (call_id IS NOT NULL)
29. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on chat_channels_org_id_fk_i (cost=0.00..4.43 rows=19 width=0) (never executed)

  • Index Cond: (org_id IS NULL)
30. 4.900 4.900 ↑ 4.5 132 50

Index Scan using chat_messages_channel_id_fk_i on chat_messages message (cost=0.43..1,455.48 rows=589 width=8) (actual time=0.005..0.098 rows=132 loops=50)

  • Index Cond: (channel_id = channel.id)
31. 19.803 19.803 ↑ 1.0 2 6,601

Index Scan using chat_message_views_message_id_fk_i on chat_message_views view (cost=0.43..0.57 rows=2 width=8) (actual time=0.002..0.003 rows=2 loops=6,601)

  • Index Cond: (message_id = message.id)
Planning time : 1.109 ms
Execution time : 47.841 ms