explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LBNW

Settings
# exclusive inclusive rows x rows loops node
1. 1,759.951 5,485.476 ↑ 9,043.4 108 1

Merge Right Join (cost=1,350,486.99..1,973,249.77 rows=976,685 width=93) (actual time=134.423..5,485.476 rows=108 loops=1)

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

CTE similar_names

3. 23.004 23.004 ↑ 4.3 234 1

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

  • Filter: ((similar_name)::text !~~* 'Ellis'::text)
  • Rows Removed by Filter: 1
4. 3,590.996 3,590.996 ↓ 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.018..3,590.996 rows=22,407,289 loops=1)

5. 0.063 134.529 ↑ 9,043.4 108 1

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

6. 0.127 134.466 ↑ 9,043.4 108 1

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

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

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

8. 23.083 23.083 ↑ 4.3 234 1

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

9. 1.170 110.916 ↓ 0.0 0 234

Bitmap Heap Scan on contributions_contributors cc (cost=27.21..1,225.92 rows=977 width=42) (actual time=0.473..0.474 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. 109.746 109.746 ↓ 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.469..0.469 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))