explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VEr8

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 175.685 ↑ 3,333.3 3 1

Limit (cost=3,265.50..408,433.99 rows=10,000 width=57) (actual time=82.634..175.685 rows=3 loops=1)

  • Output: cc.id, cc.first_name, cc.last_name, cc.original_name, ca.state, ca.city, ca.zip
  • Buffers: shared hit=10744
2.          

CTE similar_names

3. 0.051 0.051 ↑ 1.0 234 1

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

  • Output: similar_names_dev.name
  • Buffers: shared hit=2
4. 0.083 175.682 ↑ 1,150,752.3 3 1

Nested Loop Left Join (cost=3,261.16..139,877,834.86 rows=3,452,257 width=57) (actual time=82.632..175.682 rows=3 loops=1)

  • Output: cc.id, cc.first_name, cc.last_name, cc.original_name, ca.state, ca.city, ca.zip
  • Inner Unique: true
  • Filter: ((ca.state IS NULL) OR (lower((ca.state)::text) = 'ca'::text))
  • Rows Removed by Filter: 33
  • Buffers: shared hit=10744
5. 0.374 175.311 ↑ 2,533,255.3 36 1

Nested Loop (cost=3,260.73..96,288,387.54 rows=91,197,192 width=42) (actual time=21.997..175.311 rows=36 loops=1)

  • Output: cc.id, cc.first_name, cc.last_name, cc.original_name, cc.address_id
  • Buffers: shared hit=10608
6. 0.139 0.139 ↑ 1.0 234 1

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

  • Output: sm.name
  • Buffers: shared hit=2
7. 0.234 174.798 ↓ 0.0 0 234

Bitmap Heap Scan on public.contributions_contributors cc (cost=3,260.73..407,591.50 rows=389,732 width=42) (actual time=0.747..0.747 rows=0 loops=234)

  • Output: cc.id, cc.first_name, cc.last_name, cc.middle_name, cc.suffix_name, cc.original_name, cc.name_parsed, cc.occupation, cc.employer, cc.is_individual, cc.address_id
  • Recheck Cond: ((to_tsvector('simple'::regconfig, (cc.original_name)::text) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Jon'::text))) OR (to_tsvector('simple'::regconfig, (((cc.first_name)::text || ' '::text) || (cc.last_name)::text)) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Jon'::text))))
  • Heap Blocks: exact=36
  • Buffers: shared hit=10606
8. 1.638 174.564 ↓ 0.0 0 234

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

  • Buffers: shared hit=10570
9. 117.468 117.468 ↓ 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.502..0.502 rows=0 loops=234)

  • Index Cond: (to_tsvector('simple'::regconfig, (cc.original_name)::text) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Jon'::text)))
  • Buffers: shared hit=6595
10. 55.458 55.458 ↓ 0.0 0 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.237..0.237 rows=0 loops=234)

  • Index Cond: (to_tsvector('simple'::regconfig, (((cc.first_name)::text || ' '::text) || (cc.last_name)::text)) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Jon'::text)))
  • Buffers: shared hit=3975
11. 0.288 0.288 ↑ 1.0 1 36

Index Scan using contributions_addresses_pkey on public.contributions_addresses ca (cost=0.44..0.46 rows=1 width=23) (actual time=0.008..0.008 rows=1 loops=36)

  • Output: ca.id, ca.country, ca.city, ca.state, ca.address, ca.zip, ca.original_address, ca.address_parsed
  • Index Cond: (ca.id = cc.address_id)
  • Buffers: shared hit=136