explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r111

Settings
# exclusive inclusive rows x rows loops node
1. 1,775.243 6,135.502 ↑ 5,281.4 7 1

Merge Right Join (cost=1,342,000.49..1,969,536.40 rows=36,970 width=93) (actual time=92.735..6,135.502 rows=7 loops=1)

  • Merge Cond: (ca.id = cc.address_id)
  • Filter: ((ca.state IS NULL) OR (lower((ca.state)::text) = 'ca'::text))
  • Rows Removed by Filter: 101
2.          

CTE similar_names

3. 17.437 17.437 ↑ 4.3 234 1

Function Scan on get_similar_names (cost=0.25..12.75 rows=1,000 width=32) (actual time=17.207..17.437 rows=234 loops=1)

  • Filter: ((similar_name)::text !~~* 'Ellis'::text)
  • Rows Removed by Filter: 1
4. 4,314.799 4,314.799 ↓ 1.0 22,407,289 1

Index Scan using contributions_addresses_pkey on contributions_addresses ca (cost=0.44..630,881.74 rows=22,254,786 width=23) (actual time=0.013..4,314.799 rows=22,407,289 loops=1)

5. 0.087 45.460 ↑ 9,043.4 108 1

Materialize (cost=1,341,987.30..1,346,870.73 rows=976,685 width=42) (actual time=45.283..45.460 rows=108 loops=1)

6. 0.144 45.373 ↑ 9,043.4 108 1

Sort (cost=1,341,987.30..1,344,429.02 rows=976,685 width=42) (actual time=45.281..45.373 rows=108 loops=1)

  • Sort Key: cc.address_id
  • Sort Method: quicksort Memory: 33kB
7. 0.339 45.229 ↑ 9,043.4 108 1

Nested Loop (cost=18.73..1,227,219.94 rows=976,685 width=42) (actual time=17.536..45.229 rows=108 loops=1)

8. 17.512 17.512 ↑ 4.3 234 1

CTE Scan on similar_names sn1 (cost=0.00..20.00 rows=1,000 width=32) (actual time=17.208..17.512 rows=234 loops=1)

9. 1.872 27.378 ↓ 0.0 0 234

Bitmap Heap Scan on contributions_contributors cc (cost=18.73..1,217.43 rows=977 width=42) (actual time=0.113..0.117 rows=0 loops=234)

  • Recheck Cond: ((to_tsvector('simple'::regconfig, (original_name)::text) @@ to_tsquery('simple'::regconfig, regexp_replace(replace((sn1.name)::text, '&'::text, ''::text), '\s+'::text, ' & '::text, 'g'::text))) AND (to_tsvector('simple'::regconfig, (original_name)::text) @@ '''ellis'''::tsquery))
  • Heap Blocks: exact=108
10. 25.506 25.506 ↓ 0.0 0 234

Bitmap Index Scan on ix_contributions_contributors_original_name_tsv_simple (cost=0.00..18.48 rows=977 width=0) (actual time=0.109..0.109 rows=0 loops=234)

  • Index Cond: ((to_tsvector('simple'::regconfig, (original_name)::text) @@ to_tsquery('simple'::regconfig, regexp_replace(replace((sn1.name)::text, '&'::text, ''::text), '\s+'::text, ' & '::text, 'g'::text))) AND (to_tsvector('simple'::regconfig, (original_name)::text) @@ '''ellis'''::tsquery))