explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ha53

Settings
# exclusive inclusive rows x rows loops node
1. 1,305.277 1,447.269 ↑ 1,910.0 6 1

Nested Loop (cost=1,626.05..1,714,477.94 rows=11,460 width=93) (actual time=472.142..1,447.269 rows=6 loops=1)

  • Join Filter: (to_tsvector('simple'::regconfig, (cc.original_name)::text) @@ to_tsquery('simple'::regconfig, regexp_replace(replace((sn1.similar_name)::text, '&'::text, ''::text), '\s+'::text, ' & '::text, 'g'::text)))
  • Rows Removed by Join Filter: 277986
2.          

CTE similar_names

3. 20.927 20.927 ↑ 4.3 234 1

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

  • Filter: ((similar_name)::text !~~* 'Ellis'::text)
  • Rows Removed by Filter: 1
4. 21.014 21.014 ↑ 4.3 234 1

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

5. 18.636 120.978 ↑ 1.9 1,188 234

Materialize (cost=1,613.30..522,610.92 rows=2,292 width=57) (actual time=0.019..0.517 rows=1,188 loops=234)

6. 0.000 102.342 ↑ 1.9 1,188 1

Nested Loop (cost=1,613.30..522,599.46 rows=2,292 width=57) (actual time=4.365..102.342 rows=1,188 loops=1)

7. 29.725 32.261 ↑ 13.7 14,305 1

Bitmap Heap Scan on contributions_contributors cc (cost=1,612.86..216,358.98 rows=195,337 width=42) (actual time=4.184..32.261 rows=14,305 loops=1)

  • Recheck Cond: (to_tsvector('simple'::regconfig, (original_name)::text) @@ '''ellis'''::tsquery)
  • Heap Blocks: exact=12911
8. 2.536 2.536 ↑ 13.7 14,305 1

Bitmap Index Scan on ix_contributions_contributors_original_name_tsv_simple (cost=0.00..1,564.03 rows=195,337 width=0) (actual time=2.536..2.536 rows=14,305 loops=1)

  • Index Cond: (to_tsvector('simple'::regconfig, (original_name)::text) @@ '''ellis'''::tsquery)
9. 71.525 71.525 ↓ 0.0 0 14,305

Index Scan using contributions_addresses_pkey on contributions_addresses ca (cost=0.44..1.57 rows=1 width=23) (actual time=0.005..0.005 rows=0 loops=14,305)

  • Index Cond: (id = cc.address_id)
  • Filter: ((state IS NULL) OR (state ~* 'ca'::citext))
  • Rows Removed by Filter: 1