explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Je2C

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 72,012.662 ↑ 1.0 10 1

Limit (cost=4,490,506.85..4,490,506.87 rows=10 width=159) (actual time=72,012.652..72,012.662 rows=10 loops=1)

2. 2.166 72,012.651 ↑ 3,401.9 10 1

Sort (cost=4,490,506.85..4,490,591.89 rows=34,019 width=159) (actual time=72,012.650..72,012.651 rows=10 loops=1)

  • Sort Key: t.unreads DESC, t.last_message_date DESC
  • Sort Method: top-N heapsort Memory: 27kB
3. 1.220 72,010.485 ↑ 4.4 7,719 1

Subquery Scan on t (cost=4,488,887.55..4,489,771.71 rows=34,019 width=159) (actual time=72,000.559..72,010.485 rows=7,719 loops=1)

4. 3.137 72,009.265 ↑ 4.4 7,719 1

Unique (cost=4,488,887.55..4,489,431.52 rows=34,019 width=167) (actual time=72,000.556..72,009.265 rows=7,719 loops=1)

5. 37.041 72,006.128 ↑ 2.9 37,705 1

Sort (cost=4,488,887.55..4,489,159.53 rows=108,794 width=167) (actual time=72,000.555..72,006.128 rows=37,705 loops=1)

  • Sort Key: c.conv_id DESC, m.conv_message_id DESC
  • Sort Method: external merge Disk: 6536kB
6. 21.519 71,969.087 ↑ 2.9 37,705 1

WindowAgg (cost=4,472,382.88..4,474,558.76 rows=108,794 width=167) (actual time=71,941.719..71,969.087 rows=37,705 loops=1)

7. 33.604 71,947.568 ↑ 2.9 37,705 1

Sort (cost=4,472,382.88..4,472,654.86 rows=108,794 width=160) (actual time=71,941.704..71,947.568 rows=37,705 loops=1)

  • Sort Key: c.conv_id
  • Sort Method: external merge Disk: 6232kB
8. 837.180 71,913.964 ↑ 2.9 37,705 1

Hash Join (cost=150,006.65..4,458,271.38 rows=108,794 width=160) (actual time=2,348.141..71,913.964 rows=37,705 loops=1)

  • Hash Cond: (CASE WHEN (c.user_id1 = 7937926) THEN c.user_id2 ELSE c.user_id1 END = u.user_id)
9. 15,752.595 70,109.737 ↓ 1.5 160,962 1

Hash Join (cost=47,707.68..4,327,486.22 rows=108,794 width=74) (actual time=1,019.095..70,109.737 rows=160,962 loops=1)

  • Hash Cond: (m.conv_id = c.conv_id)
10. 8,038.133 53,340.992 ↑ 1.0 107,183,442 1

Append (cost=3.55..3,997,300.54 rows=107,612,011 width=66) (actual time=0.016..53,340.992 rows=107,183,442 loops=1)

11. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on conv_messages_2019_8 m (cost=3.55..18.75 rows=295 width=63) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: (created >= '2019-08-01 00:00:00'::timestamp without time zone)
  • Filter: ((from_user_id = 7937926) OR (message_type <> 30))
12. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on conv_messages_2019_8_created_idx (cost=0.00..3.48 rows=297 width=0) (actual time=0.003..0.003 rows=0 loops=1)

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

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

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

Bitmap Index Scan on conv_messages_2019_9_created_idx (cost=0.00..3.48 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)
15. 9,510.748 9,510.748 ↑ 1.0 23,998,318 1

Seq Scan on conv_messages_2019_10 m_2 (cost=0.00..728,770.63 rows=24,036,836 width=69) (actual time=0.010..9,510.748 rows=23,998,318 loops=1)

  • Filter: ((created >= '2019-08-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 7937926) OR (message_type <> 30)))
16. 8,574.144 8,574.144 ↑ 1.0 24,429,769 1

Seq Scan on conv_messages_2019_11 m_3 (cost=0.00..745,539.95 rows=24,527,654 width=70) (actual time=0.452..8,574.144 rows=24,429,769 loops=1)

  • Filter: ((created >= '2019-08-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 7937926) OR (message_type <> 30)))
17. 9,956.519 9,956.519 ↑ 1.0 24,750,817 1

Seq Scan on conv_messages_2019_12 m_4 (cost=0.00..782,017.01 rows=25,004,286 width=68) (actual time=0.275..9,956.519 rows=24,750,817 loops=1)

  • Filter: ((created >= '2019-08-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 7937926) OR (message_type <> 30)))
18. 10,750.364 10,750.364 ↑ 1.0 25,112,299 1

Seq Scan on conv_messages_2020_1 m_5 (cost=0.00..869,880.71 rows=25,150,527 width=61) (actual time=0.164..10,750.364 rows=25,112,299 loops=1)

  • Filter: ((created >= '2019-08-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 7937926) OR (message_type <> 30)))
  • Rows Removed by Filter: 1983703
19. 6,511.067 6,511.067 ↓ 1.0 8,892,239 1

Seq Scan on conv_messages_2020_2 m_6 (cost=0.00..332,975.95 rows=8,891,823 width=59) (actual time=0.012..6,511.067 rows=8,892,239 loops=1)

  • Filter: ((created >= '2019-08-01 00:00:00'::timestamp without time zone) AND ((from_user_id = 7937926) OR (message_type <> 30)))
  • Rows Removed by Filter: 1268402
20. 0.002 0.011 ↓ 0.0 0 1

Bitmap Heap Scan on conv_messages_2020_3 m_7 (cost=3.55..18.75 rows=295 width=63) (actual time=0.011..0.011 rows=0 loops=1)

  • Recheck Cond: (created >= '2019-08-01 00:00:00'::timestamp without time zone)
  • Filter: ((from_user_id = 7937926) OR (message_type <> 30))
21. 0.009 0.009 ↓ 0.0 0 1

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

  • Index Cond: (created >= '2019-08-01 00:00:00'::timestamp without time zone)
22. 31.271 1,016.150 ↓ 1.3 45,821 1

Hash (cost=47,278.89..47,278.89 rows=34,019 width=16) (actual time=1,016.150..1,016.150 rows=45,821 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2660kB
23. 944.448 984.879 ↓ 1.3 45,821 1

Bitmap Heap Scan on convs c (cost=545.23..47,278.89 rows=34,019 width=16) (actual time=46.274..984.879 rows=45,821 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: 554
  • Heap Blocks: exact=40264
24. 0.001 40.431 ↓ 0.0 0 1

BitmapOr (cost=545.23..545.23 rows=46,226 width=0) (actual time=40.431..40.431 rows=0 loops=1)

25. 40.189 40.189 ↓ 1.0 46,198 1

Bitmap Index Scan on ix_conv_user_id1 (cost=0.00..524.77 rows=45,987 width=0) (actual time=40.189..40.189 rows=46,198 loops=1)

  • Index Cond: (user_id1 = 7937926)
26. 0.241 0.241 ↓ 1.6 379 1

Bitmap Index Scan on ix_conv_user_id2 (cost=0.00..3.45 rows=238 width=0) (actual time=0.241..0.241 rows=379 loops=1)

  • Index Cond: (user_id2 = 7937926)
27. 562.278 967.047 ↑ 1.1 1,570,034 1

Hash (cost=55,182.10..55,182.10 rows=1,735,510 width=94) (actual time=967.047..967.047 rows=1,570,034 loops=1)

  • Buckets: 32768 Batches: 128 Memory Usage: 1594kB
28. 404.769 404.769 ↑ 1.1 1,570,034 1

Seq Scan on users u (cost=0.00..55,182.10 rows=1,735,510 width=94) (actual time=0.006..404.769 rows=1,570,034 loops=1)

Planning time : 2.160 ms