explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KWxF

Settings
# exclusive inclusive rows x rows loops node
1. 268.049 10,135.622 ↑ 1.0 4 1

HashAggregate (cost=3,839,649.65..3,839,649.69 rows=4 width=5) (actual time=10,135.622..10,135.622 rows=4 loops=1)

  • Group Key: modification_history.external_system
2. 1,118.549 9,867.573 ↑ 1.5 850,336 1

Hash Join (cost=590,498.43..3,836,377.04 rows=1,309,047 width=5) (actual time=6,591.506..9,867.573 rows=850,336 loops=1)

  • Hash Cond: (modification_history.user_id = users.id)
3. 2,166.024 5,513.632 ↑ 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=3,352.258..5,513.632 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. 85.562 3,347.608 ↓ 0.0 0 1

BitmapAnd (cost=383,481.21..383,481.21 rows=1,439,167 width=0) (actual time=3,347.608..3,347.608 rows=0 loops=1)

5. 1,307.859 1,307.859 ↓ 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=1,307.859..1,307.859 rows=4,522,843 loops=1)

6. 1,954.187 1,954.187 ↓ 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,954.187..1,954.187 rows=17,470,405 loops=1)

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

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

  • Buckets: 131072 Batches: 64 Memory Usage: 3241kB
8. 1,886.968 1,886.968 ↓ 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.014..1,886.968 rows=4,010,919 loops=1)

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