explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fI4S

Settings
# exclusive inclusive rows x rows loops node
1. 1,829.912 5,678.447 ↑ 5,281.4 7 1

Merge Right Join (cost=1,350,486.99..1,978,029.70 rows=36,970 width=93) (actual time=176.662..5,678.447 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. 21.097 21.097 ↑ 4.3 234 1

Function Scan on get_similar_names (cost=0.25..12.75 rows=1,000 width=36) (actual time=20.817..21.097 rows=234 loops=1)

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

Index Scan using contributions_addresses_pkey on contributions_addresses ca (cost=0.44..630,889.44 rows=22,254,786 width=23) (actual time=0.014..3,711.822 rows=22,407,289 loops=1)

5. 0.068 136.713 ↑ 9,043.4 108 1

Materialize (cost=1,350,473.80..1,355,357.23 rows=976,685 width=42) (actual time=136.580..136.713 rows=108 loops=1)

6. 0.122 136.645 ↑ 9,043.4 108 1

Sort (cost=1,350,473.80..1,352,915.52 rows=976,685 width=42) (actual time=136.578..136.645 rows=108 loops=1)

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

Nested Loop (cost=27.21..1,235,706.44 rows=976,685 width=42) (actual time=21.826..136.523 rows=108 loops=1)

8. 21.171 21.171 ↑ 4.3 234 1

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

9. 1.170 115.128 ↓ 0.0 0 234

Bitmap Heap Scan on contributions_contributors cc (cost=27.21..1,225.92 rows=977 width=42) (actual time=0.491..0.492 rows=0 loops=234)

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

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

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