explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SxIk

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 2.340 ↑ 1.0 20 1

Limit (cost=70,732.81..70,732.86 rows=20 width=153) (actual time=2.335..2.340 rows=20 loops=1)

  • Buffers: shared hit=1,481
2. 0.018 2.335 ↑ 34.4 20 1

Sort (cost=70,732.81..70,734.53 rows=688 width=153) (actual time=2.334..2.335 rows=20 loops=1)

  • Sort Key: t.unreads DESC
  • Sort Method: top-N heapsort Memory: 30kB
  • Buffers: shared hit=1,481
3. 0.008 2.317 ↑ 16.0 43 1

Subquery Scan on t (cost=70,704.04..70,714.50 rows=688 width=153) (actual time=2.258..2.317 rows=43 loops=1)

  • Buffers: shared hit=1,481
4. 0.036 2.309 ↑ 16.0 43 1

Unique (cost=70,704.04..70,707.62 rows=688 width=161) (actual time=2.256..2.309 rows=43 loops=1)

  • Buffers: shared hit=1,481
5. 0.344 2.273 ↑ 1.6 452 1

Sort (cost=70,704.04..70,705.83 rows=716 width=161) (actual time=2.256..2.273 rows=452 loops=1)

  • Sort Key: c.conv_id DESC, m.conv_message_id DESC
  • Sort Method: quicksort Memory: 123kB
  • Buffers: shared hit=1,481
6. 0.192 1.929 ↑ 1.6 452 1

WindowAgg (cost=70,655.77..70,670.09 rows=716 width=161) (actual time=1.732..1.929 rows=452 loops=1)

  • Buffers: shared hit=1,481
7. 0.133 1.737 ↑ 1.6 452 1

Sort (cost=70,655.77..70,657.56 rows=716 width=154) (actual time=1.716..1.737 rows=452 loops=1)

  • Sort Key: c.conv_id
  • Sort Method: quicksort Memory: 122kB
  • Buffers: shared hit=1,481
8. 0.049 1.604 ↑ 1.6 452 1

Nested Loop (cost=13.80..70,621.81 rows=716 width=154) (actual time=0.263..1.604 rows=452 loops=1)

  • Buffers: shared hit=1,481
9. 0.000 0.580 ↑ 9.2 75 1

Nested Loop (cost=13.23..2,830.05 rows=688 width=102) (actual time=0.051..0.580 rows=75 loops=1)

  • Buffers: shared hit=484
10. 0.177 0.201 ↑ 7.1 97 1

Bitmap Heap Scan on convs c (cost=12.80..1,037.17 rows=688 width=16) (actual time=0.035..0.201 rows=97 loops=1)

  • Recheck Cond: ((6372777 = user_id1) OR (6372777 = user_id2))
  • Filter: (((6372777 = user_id1) AND (NOT user_id1_hidden)) OR ((6372777 = user_id2) AND (NOT user_id2_hidden)))
  • Rows Removed by Filter: 31
  • Heap Blocks: exact=110
  • Buffers: shared hit=118
11. 0.001 0.024 ↓ 0.0 0 1

BitmapOr (cost=12.80..12.80 rows=924 width=0) (actual time=0.024..0.024 rows=0 loops=1)

  • Buffers: shared hit=8
12. 0.015 0.015 ↑ 7.8 88 1

Bitmap Index Scan on ix_conv_user_id1 (cost=0.00..8.99 rows=683 width=0) (actual time=0.015..0.015 rows=88 loops=1)

  • Index Cond: (user_id1 = 6,372,777)
  • Buffers: shared hit=4
13. 0.008 0.008 ↑ 5.5 44 1

Bitmap Index Scan on ix_conv_user_id2 (cost=0.00..3.47 rows=241 width=0) (actual time=0.008..0.008 rows=44 loops=1)

  • Index Cond: (user_id2 = 6,372,777)
  • Buffers: shared hit=4
14. 0.388 0.388 ↑ 1.0 1 97

Index Scan using pk_users on users u (cost=0.43..2.61 rows=1 width=94) (actual time=0.004..0.004 rows=1 loops=97)

  • Index Cond: (user_id = CASE WHEN (c.user_id1 = 6,372,777) THEN c.user_id2 ELSE c.user_id1 END)
  • Buffers: shared hit=366
15. 0.150 0.975 ↑ 34.0 6 75

Append (cost=0.56..96.49 rows=204 width=60) (actual time=0.006..0.013 rows=6 loops=75)

  • Buffers: shared hit=997
16. 0.525 0.525 ↑ 25.0 5 75

Index Scan using conv_messages_2020_1_conv_id_idx on conv_messages_2020_1 m (cost=0.56..48.10 rows=125 width=61) (actual time=0.004..0.007 rows=5 loops=75)

  • Index Cond: (conv_id = c.conv_id)
  • Filter: ((created >= '2020-01-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 6,372,777) OR (message_type <> 30)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=577
17. 0.300 0.300 ↑ 78.0 1 75

Index Scan using conv_messages_2020_2_conv_id_idx on conv_messages_2020_2 m_1 (cost=0.43..46.67 rows=78 width=59) (actual time=0.003..0.004 rows=1 loops=75)

  • Index Cond: (conv_id = c.conv_id)
  • Filter: ((created >= '2020-01-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 6,372,777) OR (message_type <> 30)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=270
18. 0.000 0.000 ↓ 0.0 0 75

Index Scan using conv_messages_2020_3_conv_id_idx on conv_messages_2020_3 m_2 (cost=0.15..0.70 rows=1 width=63) (actual time=0.000..0.000 rows=0 loops=75)

  • Index Cond: (conv_id = c.conv_id)
  • Filter: ((created >= '2020-01-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 6,372,777) OR (message_type <> 30)))
  • Buffers: shared hit=150