explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zG2W

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 15,781.980 ↑ 1.0 10 1

Limit (cost=663,276.44..663,276.47 rows=10 width=161) (actual time=15,781.958..15,781.980 rows=10 loops=1)

2. 0.446 15,781.959 ↑ 1,483.2 10 1

Sort (cost=663,276.44..663,313.52 rows=14,832 width=161) (actual time=15,781.957..15,781.959 rows=10 loops=1)

  • Sort Key: t.unreads DESC, t.last_message_date DESC
  • Sort Method: top-N heapsort Memory: 27kB
3. 0.508 15,781.513 ↑ 17.1 868 1

Subquery Scan on t (cost=662,705.50..662,955.93 rows=14,832 width=161) (actual time=15,778.632..15,781.513 rows=868 loops=1)

4. 1.335 15,781.005 ↑ 17.1 868 1

Unique (cost=662,705.50..662,807.61 rows=14,832 width=169) (actual time=15,778.630..15,781.005 rows=868 loops=1)

5. 4.988 15,779.670 ↑ 4.7 4,333 1

Sort (cost=662,705.50..662,756.55 rows=20,422 width=169) (actual time=15,778.628..15,779.670 rows=4,333 loops=1)

  • Sort Key: c.conv_id DESC, m.conv_message_id DESC
  • Sort Method: quicksort Memory: 1,334kB
6. 4.203 15,774.682 ↑ 4.7 4,333 1

WindowAgg (cost=660,835.07..661,243.51 rows=20,422 width=169) (actual time=15,769.409..15,774.682 rows=4,333 loops=1)

7. 5.640 15,770.479 ↑ 4.7 4,333 1

Sort (cost=660,835.07..660,886.12 rows=20,422 width=162) (actual time=15,769.395..15,770.479 rows=4,333 loops=1)

  • Sort Key: c.conv_id
  • Sort Method: quicksort Memory: 1,293kB
8. 3,566.576 15,764.839 ↑ 4.7 4,333 1

Hash Join (cost=124,006.05..659,373.07 rows=20,422 width=162) (actual time=273.880..15,764.839 rows=4,333 loops=1)

  • Hash Cond: (m.conv_id = c.conv_id)
9. 6,265.094 11,956.902 ↑ 1.0 13,695,894 1

Append (cost=6.45..483,806.39 rows=13,696,765 width=69) (actual time=0.013..11,956.902 rows=13,695,894 loops=1)

10. 0.001 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on conv_messages_2019_8 m (cost=6.45..21.65 rows=295 width=63) (actual time=0.003..0.003 rows=0 loops=1)

  • Recheck Cond: (created >= '2019-08-01 00:00:00'::timestamp without time zone)
  • Filter: ((from_user_id = 7,937,926) OR (message_type <> 30))
11. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on conv_messages_2019_8_created_idx (cost=0.00..6.38 rows=297 width=0) (actual time=0.001..0.002 rows=0 loops=1)

  • Index Cond: (created >= '2019-08-01 00:00:00'::timestamp without time zone)
12. 0.002 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on conv_messages_2019_9 m_1 (cost=6.45..21.65 rows=295 width=63) (actual time=0.002..0.003 rows=0 loops=1)

  • Recheck Cond: (created >= '2019-08-01 00:00:00'::timestamp without time zone)
  • Filter: ((from_user_id = 7,937,926) OR (message_type <> 30))
13. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on conv_messages_2019_9_created_idx (cost=0.00..6.38 rows=297 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (created >= '2019-08-01 00:00:00'::timestamp without time zone)
14. 5,493.032 5,493.032 ↑ 1.0 13,217,537 1

Seq Scan on conv_messages_2019_10 m_2 (cost=0.00..400,729.90 rows=13,217,537 width=69) (actual time=0.007..5,493.032 rows=13,217,537 loops=1)

  • Filter: ((created >= '2019-08-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 7,937,926) OR (message_type <> 30)))
15. 198.196 198.196 ↑ 1.0 477,118 1

Seq Scan on conv_messages_2019_11 m_3 (cost=0.00..14,483.57 rows=477,118 width=69) (actual time=0.044..198.196 rows=477,118 loops=1)

  • Filter: ((created >= '2019-08-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 7,937,926) OR (message_type <> 30)))
16. 0.196 0.196 ↓ 1.0 437 1

Seq Scan on conv_messages_2019_12 m_4 (cost=0.00..14.65 rows=436 width=36) (actual time=0.009..0.196 rows=437 loops=1)

  • Filter: ((created >= '2019-08-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 7,937,926) OR (message_type <> 30)))
17. 0.205 0.205 ↓ 1.0 461 1

Seq Scan on conv_messages_2020_1 m_5 (cost=0.00..14.63 rows=454 width=37) (actual time=0.011..0.205 rows=461 loops=1)

  • Filter: ((created >= '2019-08-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 7,937,926) OR (message_type <> 30)))
  • Rows Removed by Filter: 41
18. 0.164 0.164 ↓ 1.0 341 1

Seq Scan on conv_messages_2020_2 m_6 (cost=0.00..14.88 rows=335 width=35) (actual time=0.004..0.164 rows=341 loops=1)

  • Filter: ((created >= '2019-08-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 7,937,926) OR (message_type <> 30)))
  • Rows Removed by Filter: 15
19. 0.001 0.009 ↓ 0.0 0 1

Bitmap Heap Scan on conv_messages_2020_3 m_7 (cost=6.45..21.65 rows=295 width=63) (actual time=0.009..0.009 rows=0 loops=1)

  • Recheck Cond: (created >= '2019-08-01 00:00:00'::timestamp without time zone)
  • Filter: ((from_user_id = 7,937,926) OR (message_type <> 30))
20. 0.008 0.008 ↓ 0.0 0 1

Bitmap Index Scan on conv_messages_2020_3_created_idx (cost=0.00..6.38 rows=297 width=0) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (created >= '2019-08-01 00:00:00'::timestamp without time zone)
21. 1.469 241.361 ↑ 11.1 1,336 1

Hash (cost=123,814.19..123,814.19 rows=14,832 width=101) (actual time=241.360..241.361 rows=1,336 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 284kB
22. 13.311 239.892 ↑ 11.1 1,336 1

Nested Loop (cost=579.82..123,814.19 rows=14,832 width=101) (actual time=4.810..239.892 rows=1,336 loops=1)

23. 91.488 94.225 ↓ 1.3 18,908 1

Bitmap Heap Scan on convs c (cost=579.39..55,656.87 rows=14,832 width=16) (actual time=4.681..94.225 rows=18,908 loops=1)

  • Recheck Cond: ((7937926 = user_id1) OR (7937926 = user_id2))
  • Filter: (((7937926 = user_id1) AND (NOT user_id1_hidden)) OR ((7937926 = user_id2) AND (NOT user_id2_hidden)))
  • Rows Removed by Filter: 238
  • Heap Blocks: exact=17,814
24. 0.001 2.737 ↓ 0.0 0 1

BitmapOr (cost=579.39..579.39 rows=20,647 width=0) (actual time=2.737..2.737 rows=0 loops=1)

25. 2.709 2.709 ↑ 1.1 19,001 1

Bitmap Index Scan on ix_conv_user_id1 (cost=0.00..566.74 rows=20,558 width=0) (actual time=2.709..2.709 rows=19,001 loops=1)

  • Index Cond: (user_id1 = 7,937,926)
26. 0.027 0.027 ↓ 1.6 145 1

Bitmap Index Scan on ix_conv_user_id2 (cost=0.00..5.23 rows=89 width=0) (actual time=0.027..0.027 rows=145 loops=1)

  • Index Cond: (user_id2 = 7,937,926)
27. 132.356 132.356 ↓ 0.0 0 18,908

Index Scan using pk_users on users u (cost=0.43..4.60 rows=1 width=93) (actual time=0.007..0.007 rows=0 loops=18,908)

  • Index Cond: (user_id = CASE WHEN (c.user_id1 = 7,937,926) THEN c.user_id2 ELSE c.user_id1 END)