explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lDV4

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 14.149 ↑ 1.0 25 1

Limit (cost=3,836.97..3,837.03 rows=25 width=574) (actual time=14.145..14.149 rows=25 loops=1)

2. 0.018 14.145 ↑ 1.5 25 1

Sort (cost=3,836.97..3,837.06 rows=37 width=574) (actual time=14.144..14.145 rows=25 loops=1)

  • Sort Key: (GREATEST(chat_messages_cte.max_created, chat_group.created))
  • Sort Method: quicksort Memory: 53kB
3. 0.073 14.127 ↑ 1.0 37 1

WindowAgg (cost=5.31..3,836.01 rows=37 width=574) (actual time=14.111..14.127 rows=37 loops=1)

4. 0.045 14.054 ↑ 1.0 37 1

Nested Loop Left Join (cost=5.31..3,835.45 rows=37 width=558) (actual time=2.849..14.054 rows=37 loops=1)

5. 0.066 5.240 ↑ 1.0 37 1

Nested Loop Left Join (cost=0.29..333.91 rows=37 width=538) (actual time=2.591..5.240 rows=37 loops=1)

6. 0.290 0.290 ↑ 1.0 37 1

Seq Scan on direct_chat_group (cost=0.00..42.53 rows=37 width=24) (actual time=0.011..0.290 rows=37 loops=1)

  • Filter: ((account_id_one = 90) OR (account_id_two = 90))
  • Rows Removed by Filter: 1,932
7. 4.884 4.884 ↑ 1.0 1 37

Index Scan using chat_group_pkey on chat_group (cost=0.29..7.88 rows=1 width=514) (actual time=0.132..0.132 rows=1 loops=37)

  • Index Cond: (id = direct_chat_group.chat_group_id)
8. 0.037 8.769 ↑ 1.0 1 37

Subquery Scan on chat_messages_cte (cost=5.02..94.63 rows=1 width=20) (actual time=0.237..0.237 rows=1 loops=37)

  • Filter: (chat_group.id = chat_messages_cte.chat_group_id)
9. 0.222 8.732 ↑ 10.0 1 37

GroupAggregate (cost=5.02..94.50 rows=10 width=20) (actual time=0.236..0.236 rows=1 loops=37)

  • Group Key: cm.chat_group_id
10. 0.140 8.510 ↓ 1.7 17 37

Nested Loop Left Join (cost=5.02..94.33 rows=10 width=12) (actual time=0.179..0.230 rows=17 loops=37)

  • Join Filter: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
  • Rows Removed by Filter: 2
11. 1.850 4.810 ↑ 1.1 19 37

Bitmap Heap Scan on chat_message cm (cost=4.59..85.48 rows=21 width=12) (actual time=0.085..0.130 rows=19 loops=37)

  • Recheck Cond: (chat_group.id = chat_group_id)
  • Heap Blocks: exact=711
12. 2.960 2.960 ↑ 1.1 19 37

Bitmap Index Scan on chat_message_chat_group_id (cost=0.00..4.58 rows=21 width=0) (actual time=0.080..0.080 rows=19 loops=37)

  • Index Cond: (chat_group_id = chat_group.id)
13. 0.341 3.560 ↓ 0.0 0 712

Materialize (cost=0.43..8.48 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=712)

14. 0.000 3.219 ↓ 0.0 0 37

Subquery Scan on cgr (cost=0.43..8.47 rows=1 width=12) (actual time=0.087..0.087 rows=0 loops=37)

15. 0.037 3.219 ↓ 0.0 0 37

GroupAggregate (cost=0.43..8.46 rows=1 width=12) (actual time=0.087..0.087 rows=0 loops=37)

  • Group Key: chat_group_read.chat_group_id
16. 3.182 3.182 ↓ 0.0 0 37

Index Only Scan using chat_group_read_main on chat_group_read (cost=0.43..8.45 rows=1 width=12) (actual time=0.086..0.086 rows=0 loops=37)

  • Index Cond: ((chat_group_id = chat_group.id) AND (account_id = 90))
  • Heap Fetches: 8
Planning time : 0.399 ms
Execution time : 14.212 ms