explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7tnj

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

Hash Left Join (cost=97,146,898.44..114,425,020.73 rows=3,452,258 width=639) (actual time=10,181.988..11,583.162 rows=13 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: 192
  • Buffers: shared hit=231099, temp read=62088 written=123020
2.          

CTE cc

3. 0.458 151.905 ↑ 444,864.4 205 1

Nested Loop (cost=3,260.73..96,288,387.20 rows=91,197,192 width=42) (actual time=1.851..151.905 rows=205 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=9646
4. 0.049 0.049 ↑ 1.0 234 1

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

  • Output: sm.name
  • Buffers: shared hit=2
5. 0.468 151.398 ↑ 389,732.0 1 234

Bitmap Heap Scan on public.contributions_contributors cc_1 (cost=3,260.73..407,591.50 rows=389,732 width=42) (actual time=0.645..0.647 rows=1 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: ((to_tsvector('simple'::regconfig, (cc_1.original_name)::text) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Ellis'::text))) OR (to_tsvector('simple'::regconfig, (((cc_1.first_name)::text || ' '::text) || (cc_1.last_name)::text)) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Ellis'::text))))
  • Heap Blocks: exact=205
  • Buffers: shared hit=9644
6. 1.638 150.930 ↓ 0.0 0 234

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

  • Buffers: shared hit=9439
7. 107.640 107.640 ↓ 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.460..0.460 rows=0 loops=234)

  • Index Cond: (to_tsvector('simple'::regconfig, (cc_1.original_name)::text) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Ellis'::text)))
  • Buffers: shared hit=6165
8. 41.652 41.652 ↑ 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.178..0.178 rows=1 loops=234)

  • Index Cond: (to_tsvector('simple'::regconfig, (((cc_1.first_name)::text || ' '::text) || (cc_1.last_name)::text)) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Ellis'::text)))
  • Buffers: shared hit=3274
9. 152.025 152.025 ↑ 444,864.4 205 1

CTE Scan on cc (cost=0.00..1,823,943.84 rows=91,197,192 width=588) (actual time=1.854..152.025 rows=205 loops=1)

  • Output: cc.id, cc.first_name, cc.last_name, cc.original_name, cc.address_id
  • Buffers: shared hit=9646
10. 5,519.388 10,046.642 ↑ 1.0 22,463,744 1

Hash (cost=446,090.44..446,090.44 rows=22,463,744 width=23) (actual time=10,046.642..10,046.642 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
11. 4,527.254 4,527.254 ↑ 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.007..4,527.254 rows=22,463,744 loops=1)

  • Output: ca.state, ca.city, ca.zip, ca.id
  • Buffers: shared hit=221453
Planning time : 1.650 ms
Execution time : 11,583.705 ms