explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cssJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 24,261.178 ↑ 3.3 6 1

Limit (cost=2,168,261.30..2,168,261.35 rows=20 width=16) (actual time=24,261.177..24,261.178 rows=6 loops=1)

2. 0.005 24,261.176 ↑ 33.3 6 1

Sort (cost=2,168,261.30..2,168,261.80 rows=200 width=16) (actual time=24,261.176..24,261.176 rows=6 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.000 24,261.171 ↑ 33.3 6 1

Finalize GroupAggregate (cost=2,168,173.19..2,168,255.98 rows=200 width=16) (actual time=24,261.167..24,261.171 rows=6 loops=1)

  • Group Key: m_11.conv_id
4. 34.502 24,278.621 ↑ 66.7 6 1

Gather Merge (cost=2,168,173.19..2,168,251.98 rows=400 width=16) (actual time=24,261.162..24,278.621 rows=6 loops=1)

  • Workers Planned: 2
  • Workers Launched: 1
5. 0.014 24,244.119 ↑ 66.7 3 2 / 2

Partial GroupAggregate (cost=2,167,173.17..2,167,205.79 rows=200 width=16) (actual time=24,244.117..24,244.119 rows=3 loops=2)

  • Group Key: m_11.conv_id
6. 0.031 24,244.105 ↑ 1,360.7 3 2 / 2

Sort (cost=2,167,173.17..2,167,183.38 rows=4,082 width=8) (actual time=24,244.104..24,244.105 rows=3 loops=2)

  • Sort Key: m_11.conv_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
7. 1,034.551 24,244.074 ↑ 1,360.7 3 2 / 2

Parallel Hash Join (cost=47,145.14..2,166,928.35 rows=4,082 width=8) (actual time=6,972.117..24,244.074 rows=3 loops=2)

  • Hash Cond: (m_11.conv_id = convs.conv_id)
8. 295.064 22,513.957 ↓ 1.2 3,554,586 2 / 2

Parallel Append (cost=0.00..2,111,983.14 rows=2,971,457 width=8) (actual time=0.147..22,513.957 rows=3,554,586 loops=2)

9. 5,721.677 5,721.677 ↓ 2.4 4,282,794 1 / 2

Parallel Seq Scan on conv_messages_2020_1 m_11 (cost=0.00..536,491.08 rows=1,806,718 width=8) (actual time=0.278..11,443.355 rows=4,282,794 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
  • Rows Removed by Filter: 22813208
10. 4,693.908 4,693.908 ↓ 2.4 933,259 1 / 2

Parallel Seq Scan on conv_messages_2019_12 m_10 (cost=0.00..473,354.50 rows=385,525 width=8) (actual time=0.532..9,387.817 rows=933,259 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
  • Rows Removed by Filter: 23817558
11. 4,775.821 4,775.821 ↓ 0.0 0 2 / 2

Parallel Seq Scan on conv_messages_2019_11 m_9 (cost=0.00..443,545.38 rows=1 width=8) (actual time=4,775.821..4,775.821 rows=0 loops=2)

  • Filter: (unread AND (from_user_id <> 7937926))
  • Rows Removed by Filter: 12214884
12. 4,417.870 4,417.870 ↓ 0.0 0 1 / 2

Parallel Seq Scan on conv_messages_2019_10 m_8 (cost=0.00..433,137.24 rows=1 width=8) (actual time=8,835.741..8,835.741 rows=0 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
  • Rows Removed by Filter: 23998318
13. 2,609.613 2,609.613 ↓ 2.4 1,893,118 1 / 2

Parallel Seq Scan on conv_messages_2020_2 m_12 (cost=0.00..210,448.76 rows=777,547 width=8) (actual time=0.008..5,219.227 rows=1,893,118 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
  • Rows Removed by Filter: 8338010
14. 0.000 0.000 ↓ 0.0 0 1 / 2

Parallel Seq Scan on conv_messages_2018_7 m (cost=0.00..16.54 rows=261 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
15. 0.000 0.000 ↓ 0.0 0 1 / 2

Parallel Seq Scan on conv_messages_2018_8 m_1 (cost=0.00..16.54 rows=261 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
16. 0.000 0.000 ↓ 0.0 0 1 / 2

Parallel Seq Scan on conv_messages_2019_4 m_2 (cost=0.00..16.54 rows=261 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
17. 0.000 0.000 ↓ 0.0 0 1 / 2

Parallel Seq Scan on conv_messages_2019_5 m_3 (cost=0.00..16.54 rows=261 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
18. 0.000 0.000 ↓ 0.0 0 1 / 2

Parallel Seq Scan on conv_messages_2019_6 m_4 (cost=0.00..16.54 rows=261 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
19. 0.001 0.001 ↓ 0.0 0 1 / 2

Parallel Seq Scan on conv_messages_2019_7 m_5 (cost=0.00..16.54 rows=261 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
20. 0.000 0.000 ↓ 0.0 0 1 / 2

Parallel Seq Scan on conv_messages_2019_8 m_6 (cost=0.00..16.54 rows=261 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
21. 0.000 0.000 ↓ 0.0 0 1 / 2

Parallel Seq Scan on conv_messages_2019_9 m_7 (cost=0.00..16.54 rows=261 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
22. 0.001 0.001 ↓ 0.0 0 1 / 2

Parallel Seq Scan on conv_messages_2020_3 m_13 (cost=0.00..16.54 rows=261 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (unread AND (from_user_id <> 7937926))
23. 19.701 695.566 ↓ 1.2 23,192 2 / 2

Parallel Hash (cost=46,904.26..46,904.26 rows=19,270 width=8) (actual time=695.566..695.566 rows=23,192 loops=2)

  • Buckets: 65536 Batches: 1 Memory Usage: 2336kB
24. 653.543 675.865 ↓ 1.2 23,192 2 / 2

Parallel Bitmap Heap Scan on convs (cost=551.52..46,904.26 rows=19,270 width=8) (actual time=37.713..675.865 rows=23,192 loops=2)

  • Recheck Cond: ((user_id1 = 7937926) OR (user_id2 = 7937926))
  • Heap Blocks: exact=24097
25. 0.001 22.322 ↓ 0.0 0 1 / 2

BitmapOr (cost=551.52..551.52 rows=46,249 width=0) (actual time=44.644..44.644 rows=0 loops=1)

26. 22.209 22.209 ↓ 1.0 46,206 1 / 2

Bitmap Index Scan on ix_conv_user_id1 (cost=0.00..524.95 rows=46,011 width=0) (actual time=44.419..44.419 rows=46,206 loops=1)

  • Index Cond: (user_id1 = 7937926)
27. 0.112 0.112 ↓ 1.6 379 1 / 2

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

  • Index Cond: (user_id2 = 7937926)
Planning time : 7.176 ms