explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KnbQ2

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 3.081 ↓ 3.0 3 1

Limit (cost=4,442.69..4,442.69 rows=1 width=574) (actual time=3.080..3.081 rows=3 loops=1)

2. 0.019 3.079 ↓ 3.0 3 1

Sort (cost=4,442.69..4,442.69 rows=1 width=574) (actual time=3.079..3.079 rows=3 loops=1)

  • Sort Key: (GREATEST(chat_messages_cte.max_created, chat_group.created))
  • Sort Method: top-N heapsort Memory: 29kB
3. 0.074 3.060 ↓ 43.0 43 1

WindowAgg (cost=5.17..4,442.68 rows=1 width=574) (actual time=3.041..3.060 rows=43 loops=1)

4. 0.054 2.986 ↓ 43.0 43 1

Nested Loop (cost=5.17..4,442.66 rows=1 width=558) (actual time=0.091..2.986 rows=43 loops=1)

  • Join Filter: (direct_chat_group.chat_group_id = chat_messages_cte.chat_group_id)
5. 0.030 0.524 ↑ 1.0 43 1

Nested Loop (cost=0.29..379.76 rows=43 width=538) (actual time=0.030..0.524 rows=43 loops=1)

6. 0.322 0.322 ↑ 1.0 43 1

Seq Scan on direct_chat_group (cost=0.00..42.53 rows=43 width=24) (actual time=0.020..0.322 rows=43 loops=1)

  • Filter: ((account_id_one = 1) OR (account_id_two = 1))
  • Rows Removed by Filter: 1,926
7. 0.172 0.172 ↑ 1.0 1 43

Index Scan using chat_group_pkey on chat_group (cost=0.29..7.84 rows=1 width=514) (actual time=0.004..0.004 rows=1 loops=43)

  • Index Cond: (id = direct_chat_group.chat_group_id)
8. 0.000 2.408 ↑ 1.0 1 43

Subquery Scan on chat_messages_cte (cost=4.88..94.47 rows=1 width=20) (actual time=0.056..0.056 rows=1 loops=43)

  • Filter: (chat_group.id = chat_messages_cte.chat_group_id)
9. 0.301 2.408 ↑ 10.0 1 43

GroupAggregate (cost=4.88..94.35 rows=10 width=20) (actual time=0.056..0.056 rows=1 loops=43)

  • Group Key: cm.chat_group_id
10. 0.688 2.107 ↓ 2.1 21 43

Nested Loop Left Join (cost=4.88..94.17 rows=10 width=12) (actual time=0.016..0.049 rows=21 loops=43)

  • Join Filter: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
11. 1.161 1.419 ↑ 1.0 21 43

Bitmap Heap Scan on chat_message cm (cost=4.59..85.48 rows=21 width=12) (actual time=0.009..0.033 rows=21 loops=43)

  • Recheck Cond: (chat_group.id = chat_group_id)
  • Heap Blocks: exact=906
12. 0.258 0.258 ↑ 1.0 21 43

Bitmap Index Scan on chat_message_chat_group_id (cost=0.00..4.58 rows=21 width=0) (actual time=0.006..0.006 rows=21 loops=43)

  • Index Cond: (chat_group_id = chat_group.id)
13. 0.000 0.000 ↓ 0.0 0 907

Materialize (cost=0.29..8.32 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=907)

14. 0.000 0.129 ↓ 0.0 0 43

Subquery Scan on cgr (cost=0.29..8.32 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=43)

15. 0.129 0.129 ↓ 0.0 0 43

Index Only Scan Backward using chat_group_read_main on chat_group_read (cost=0.29..8.31 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=43)

  • Index Cond: ((chat_group_id = chat_group.id) AND (account_id = 1))
  • Heap Fetches: 0
Planning time : 0.643 ms
Execution time : 3.161 ms