explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QC7k

Settings
# exclusive inclusive rows x rows loops node
1. 268.228 8,306.566 ↑ 1.0 4 1

HashAggregate (cost=3,839,649.65..3,839,649.69 rows=4 width=5) (actual time=8,306.565..8,306.566 rows=4 loops=1)

  • Group Key: modification_history.external_system
2. 1,125.684 8,038.338 ↑ 1.5 850,336 1

Hash Join (cost=590,498.43..3,836,377.04 rows=1,309,047 width=5) (actual time=5,368.534..8,038.338 rows=850,336 loops=1)

  • Hash Cond: (modification_history.user_id = users.id)
3. 1,549.650 3,907.652 ↑ 1.2 1,249,849 1

Bitmap Heap Scan on modification_history (cost=383,481.21..3,575,931.59 rows=1,439,167 width=9) (actual time=2,362.240..3,907.652 rows=1,249,849 loops=1)

  • Recheck Cond: ((creation_type IS NOT NULL) AND (external_system IS NOT NULL))
  • Rows Removed by Index Recheck: 4896889
  • Heap Blocks: exact=9674 lossy=665302
4. 82.680 2,358.002 ↓ 0.0 0 1

BitmapAnd (cost=383,481.21..383,481.21 rows=1,439,167 width=0) (actual time=2,358.001..2,358.002 rows=0 loops=1)

5. 557.716 557.716 ↓ 1.0 4,522,843 1

Bitmap Index Scan on partial_index_on_user_id_where_creation_type_not_null (cost=0.00..69,579.98 rows=4,356,710 width=0) (actual time=557.715..557.716 rows=4,522,843 loops=1)

6. 1,717.606 1,717.606 ↓ 1.0 17,470,405 1

Bitmap Index Scan on test (cost=0.00..313,181.39 rows=16,957,977 width=0) (actual time=1,717.606..1,717.606 rows=17,470,405 loops=1)

  • Index Cond: (external_system IS NOT NULL)
7. 1,286.295 3,005.002 ↓ 1.0 4,010,919 1

Hash (cost=141,969.73..141,969.73 rows=3,964,760 width=4) (actual time=3,005.001..3,005.002 rows=4,010,919 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 3241kB
8. 1,718.707 1,718.707 ↓ 1.0 4,010,919 1

Seq Scan on users (cost=0.00..141,969.73 rows=3,964,760 width=4) (actual time=0.008..1,718.707 rows=4,010,919 loops=1)

  • Filter: ((NOT deleted) AND ((user_type)::text = 'candidate'::text))
  • Rows Removed by Filter: 347063