explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Wm

Settings
# exclusive inclusive rows x rows loops node
1. 271.027 13,976.997 ↑ 1.0 4 1

HashAggregate (cost=5,552,060.61..5,552,060.65 rows=4 width=5) (actual time=13,976.996..13,976.997 rows=4 loops=1)

  • Group Key: modification_history.external_system
2. 1,216.254 13,705.970 ↑ 1.5 850,336 1

Hash Join (cost=276,980.45..5,548,786.89 rows=1,309,488 width=5) (actual time=5,542.244..13,705.970 rows=850,336 loops=1)

  • Hash Cond: (modification_history.user_id = users.id)
3. 8,557.661 9,175.922 ↑ 1.2 1,249,849 1

Bitmap Heap Scan on modification_history (cost=69,963.22..5,288,329.40 rows=1,439,651 width=9) (actual time=2,227.287..9,175.922 rows=1,249,849 loops=1)

  • Recheck Cond: (creation_type IS NOT NULL)
  • Rows Removed by Index Recheck: 12648443
  • Filter: (external_system IS NOT NULL)
  • Rows Removed by Filter: 3272994
  • Heap Blocks: exact=13201 lossy=1384521
4. 618.261 618.261 ↓ 1.0 4,522,843 1

Bitmap Index Scan on partial_index_on_user_id_where_creation_type_not_null (cost=0.00..69,603.31 rows=4,358,175 width=0) (actual time=618.260..618.261 rows=4,522,843 loops=1)

5. 1,377.646 3,313.794 ↓ 1.0 4,010,919 1

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

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

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