explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zNBI

Settings
# exclusive inclusive rows x rows loops node
1. 1,407.381 11,048.710 ↑ 265,558.3 13 1

Hash Left Join (cost=861,776.31..100,488,953.57 rows=3,452,258 width=93) (actual time=9,625.386..11,048.710 rows=13 loops=1)

  • Output: cc_subq.id, cc_subq.first_name, cc_subq.last_name, cc_subq.original_name, ca.state, ca.city, ca.zip, 'state'::text, 0
  • Inner Unique: true
  • Hash Cond: (cc_subq.address_id = ca.id)
  • Filter: ((ca.state IS NULL) OR (lower((ca.state)::text) = 'ca'::text))
  • Rows Removed by Filter: 192
  • Buffers: shared hit=231099, temp read=60154 written=123014
2.          

CTE similar_names

3. 0.047 0.047 ↑ 1.0 234 1

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

  • Output: similar_names_dev.name
  • Buffers: shared hit=2
4. 0.355 142.522 ↑ 444,864.4 205 1

Nested Loop (cost=3,260.73..96,288,387.54 rows=91,197,192 width=42) (actual time=2.195..142.522 rows=205 loops=1)

  • Output: cc_subq.id, cc_subq.first_name, cc_subq.last_name, cc_subq.original_name, cc_subq.address_id
  • Buffers: shared hit=9646
5. 0.129 0.129 ↑ 1.0 234 1

CTE Scan on similar_names sm (cost=0.00..4.68 rows=234 width=32) (actual time=0.015..0.129 rows=234 loops=1)

  • Output: sm.name
  • Buffers: shared hit=2
6. 0.702 142.038 ↑ 389,732.0 1 234

Bitmap Heap Scan on public.contributions_contributors cc_subq (cost=3,260.73..407,591.50 rows=389,732 width=42) (actual time=0.605..0.607 rows=1 loops=234)

  • Output: cc_subq.id, cc_subq.first_name, cc_subq.last_name, cc_subq.middle_name, cc_subq.suffix_name, cc_subq.original_name, cc_subq.name_parsed, cc_subq.occupation, cc_subq.employer, cc_subq.is_individual, cc_subq.address_id
  • Recheck Cond: ((to_tsvector('simple'::regconfig, (cc_subq.original_name)::text) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Jon'::text))) OR (to_tsvector('simple'::regconfig, (((cc_subq.first_name)::text || ' '::text) || (cc_subq.last_name)::text)) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Jon'::text))))
  • Heap Blocks: exact=205
  • Buffers: shared hit=9644
7. 1.404 141.336 ↓ 0.0 0 234

BitmapOr (cost=3,260.73..3,260.73 rows=390,708 width=0) (actual time=0.604..0.604 rows=0 loops=234)

  • Buffers: shared hit=9439
8. 100.854 100.854 ↓ 0.0 0 234

Bitmap Index Scan on ix_contributions_contributors_original_name_tsv_simple (cost=0.00..1,537.31 rows=195,354 width=0) (actual time=0.431..0.431 rows=0 loops=234)

  • Index Cond: (to_tsvector('simple'::regconfig, (cc_subq.original_name)::text) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Jon'::text)))
  • Buffers: shared hit=6165
9. 39.078 39.078 ↑ 195,354.0 1 234

Bitmap Index Scan on ix_contributions_contributors_name_tsv_simple (cost=0.00..1,528.55 rows=195,354 width=0) (actual time=0.167..0.167 rows=1 loops=234)

  • Index Cond: (to_tsvector('simple'::regconfig, (((cc_subq.first_name)::text || ' '::text) || (cc_subq.last_name)::text)) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Jon'::text)))
  • Buffers: shared hit=3274
10. 5,334.362 9,498.807 ↑ 1.0 22,463,744 1

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

  • Output: ca.state, ca.city, ca.zip, ca.id
  • Buckets: 262144 Batches: 256 Memory Usage: 7001kB
  • Buffers: shared hit=221453, temp written=122635
11. 4,164.445 4,164.445 ↑ 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,164.445 rows=22,463,744 loops=1)

  • Output: ca.state, ca.city, ca.zip, ca.id
  • Buffers: shared hit=221453