explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ao1B

Settings
# exclusive inclusive rows x rows loops node
1. 2,564.799 81,764.312 ↑ 81.1 21,346 1

Hash Left Join (cost=2,933,243.50..11,659,604.50 rows=1,730,455 width=639) (actual time=10,130.838..81,764.312 rows=21,346 loops=1)

  • Output: cc.id, cc.first_name, cc.last_name, cc.original_name, ca.state, ca.city, ca.zip, 'state'::text, 0
  • Inner Unique: true
  • Hash Cond: (cc.address_id = ca.id)
  • Filter: ((ca.state IS NULL) OR (lower((ca.state)::text) = 'ca'::text))
  • Rows Removed by Filter: 180888
  • Buffers: shared hit=2848314 read=82118, temp read=125038 written=125837
2.          

CTE cc

3. 52.666 68,975.151 ↑ 226.0 202,234 1

Nested Loop (cost=1,592.59..2,074,732.26 rows=45,712,878 width=42) (actual time=20.902..68,975.151 rows=202,234 loops=1)

  • Output: cc_1.id, cc_1.first_name, cc_1.last_name, cc_1.original_name, cc_1.address_id
  • Buffers: shared hit=2626861 read=82118
4. 0.125 0.125 ↑ 1.0 234 1

Seq Scan on public.similar_names_dev sm (cost=0.00..4.34 rows=234 width=7) (actual time=0.009..0.125 rows=234 loops=1)

  • Output: sm.name
  • Buffers: shared hit=2
5. 51,480.234 68,922.360 ↑ 226.1 864 234

Bitmap Heap Scan on public.contributions_contributors cc_1 (cost=1,592.59..6,912.82 rows=195,354 width=42) (actual time=78.648..294.540 rows=864 loops=234)

  • Output: cc_1.id, cc_1.first_name, cc_1.last_name, cc_1.middle_name, cc_1.suffix_name, cc_1.original_name, cc_1.name_parsed, cc_1.occupation, cc_1.employer, cc_1.is_individual, cc_1.address_id
  • Recheck Cond: ((cc_1.original_name)::text ~* (('\y'::text || (sm.name)::text) || '\y'::text))
  • Rows Removed by Index Recheck: 245931
  • Heap Blocks: exact=2013321 lossy=516172
  • Buffers: shared hit=2626859 read=82118
6. 17,442.126 17,442.126 ↑ 4.3 45,110 234

Bitmap Index Scan on ix_contributions_contributors_original_name_idx_t (cost=0.00..1,543.75 rows=195,354 width=0) (actual time=74.539..74.539 rows=45,110 loops=234)

  • Index Cond: ((cc_1.original_name)::text ~* (('\y'::text || (sm.name)::text) || '\y'::text))
  • Buffers: shared hit=97366 read=82118
7. 69,090.943 69,090.943 ↑ 226.0 202,234 1

CTE Scan on cc (cost=0.00..914,257.56 rows=45,712,878 width=588) (actual time=20.904..69,090.943 rows=202,234 loops=1)

  • Output: cc.id, cc.first_name, cc.last_name, cc.original_name, cc.address_id
  • Buffers: shared hit=2626861 read=82118, temp written=1309
8. 5,535.066 10,108.570 ↑ 1.0 22,463,744 1

Hash (cost=446,090.44..446,090.44 rows=22,463,744 width=23) (actual time=10,108.570..10,108.570 rows=22,463,744 loops=1)

  • Output: ca.state, ca.city, ca.zip, ca.id
  • Buckets: 262144 Batches: 256 Memory Usage: 6949kB
  • Buffers: shared hit=221453, temp written=122637
9. 4,573.504 4,573.504 ↑ 1.0 22,463,744 1

Seq Scan on public.contributions_addresses ca (cost=0.00..446,090.44 rows=22,463,744 width=23) (actual time=0.008..4,573.504 rows=22,463,744 loops=1)

  • Output: ca.state, ca.city, ca.zip, ca.id
  • Buffers: shared hit=221453
Planning time : 0.636 ms
Execution time : 81,772.952 ms