explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H7Ai

Settings
# exclusive inclusive rows x rows loops node
1. 1,247.781 10,798.091 ↑ 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,534.209..10,798.091 rows=13 loops=1)

  • Hash Cond: (cc_s.address_id = ca.id)
  • Filter: ((ca.state IS NULL) OR (lower((ca.state)::text) = 'ca'::text))
  • Rows Removed by Filter: 192
2.          

CTE similar_names

3. 0.049 0.049 ↑ 1.0 234 1

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

4. 0.391 143.025 ↑ 444,864.4 205 1

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

5. 0.128 0.128 ↑ 1.0 234 1

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

6. 0.468 142.506 ↑ 389,732.0 1 234

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

  • Recheck Cond: ((to_tsvector('simple'::regconfig, (original_name)::text) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Ellis'::text))) OR (to_tsvector('simple'::regconfig, (((first_name)::text || ' '::text) || (last_name)::text)) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Ellis'::text))))
  • Heap Blocks: exact=205
7. 1.638 142.038 ↓ 0.0 0 234

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

8. 101.322 101.322 ↓ 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.433..0.433 rows=0 loops=234)

  • Index Cond: (to_tsvector('simple'::regconfig, (original_name)::text) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Ellis'::text)))
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, (((first_name)::text || ' '::text) || (last_name)::text)) @@ plainto_tsquery('simple'::regconfig, (((sm.name)::text || ' '::text) || 'Ellis'::text)))
10. 5,234.310 9,407.285 ↑ 1.0 22,463,744 1

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

  • Buckets: 262144 Batches: 256 Memory Usage: 7001kB
11. 4,172.975 4,172.975 ↑ 1.0 22,463,744 1

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

Planning time : 2.389 ms
Execution time : 10,798.226 ms