explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yw0Y

Settings
# exclusive inclusive rows x rows loops node
1. 270.014 16,391.584 ↑ 1.0 4 1

HashAggregate (cost=6,545,557.72..6,545,557.76 rows=4 width=5) (actual time=16,391.582..16,391.584 rows=4 loops=1)

  • Group Key: modification_history.external_system
2.          

CTE candidates

3. 1,707.545 1,707.545 ↓ 1.0 4,010,919 1

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

  • Filter: ((deleted IS FALSE) AND ((user_type)::text = 'candidate'::text))
  • Rows Removed by Filter: 347063
4. 1,528.673 16,121.570 ↑ 135.5 850,336 1

Merge Join (cost=4,369,958.38..6,115,513.44 rows=115,229,822 width=5) (actual time=13,327.588..16,121.570 rows=850,336 loops=1)

  • Merge Cond: (modification_history.user_id = candidates.id)
5. 1,267.901 5,157.010 ↑ 1.2 1,249,849 1

Sort (cost=3,747,733.37..3,751,331.28 rows=1,439,167 width=9) (actual time=4,706.095..5,157.010 rows=1,249,849 loops=1)

  • Sort Key: modification_history.user_id
  • Sort Method: external merge Disk: 24856kB
6. 1,505.048 3,889.109 ↑ 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,388.791..3,889.109 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
7. 83.785 2,384.061 ↓ 0.0 0 1

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

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

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

  • Index Cond: (external_system IS NOT NULL)
10. 1,607.926 9,435.887 ↓ 1.0 4,010,919 1

Materialize (cost=622,225.01..642,048.81 rows=3,964,759 width=4) (actual time=6,388.117..9,435.887 rows=4,010,919 loops=1)

11. 3,904.505 7,827.961 ↓ 1.0 4,010,919 1

Sort (cost=622,225.01..632,136.91 rows=3,964,759 width=4) (actual time=6,388.113..7,827.961 rows=4,010,919 loops=1)

  • Sort Key: candidates.id
  • Sort Method: external merge Disk: 54896kB
12. 3,923.456 3,923.456 ↓ 1.0 4,010,919 1

CTE Scan on candidates (cost=0.00..79,295.18 rows=3,964,759 width=4) (actual time=0.020..3,923.456 rows=4,010,919 loops=1)