explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CXhM

Settings
# exclusive inclusive rows x rows loops node
1. 1,779.643 5,555.928 ↑ 9,127.9 107 1

Merge Join (cost=1,350,488.58..1,976,476.99 rows=976,685 width=93) (actual time=131.326..5,555.928 rows=107 loops=1)

  • Merge Cond: (ca.id = cc.address_id)
2.          

CTE similar_names

3. 19.288 19.288 ↑ 4.3 234 1

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

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

Index Scan using contributions_addresses_pkey on contributions_addresses ca (cost=0.44..634,023.81 rows=22,463,744 width=23) (actual time=0.013..3,644.850 rows=22,407,289 loops=1)

5. 0.063 131.435 ↑ 9,043.4 108 1

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

6. 0.123 131.372 ↑ 9,043.4 108 1

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

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

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

8. 19.354 19.354 ↑ 4.3 234 1

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

9. 0.936 111.618 ↓ 0.0 0 234

Bitmap Heap Scan on contributions_contributors cc (cost=27.21..1,225.92 rows=977 width=42) (actual time=0.476..0.477 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. 110.682 110.682 ↓ 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.473..0.473 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))
Planning time : 1.159 ms
Execution time : 5,556.071 ms