explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GThY

Settings
# exclusive inclusive rows x rows loops node
1. 81.033 20,566.485 ↑ 6,761.7 6 1

Hash Join (cost=289,666.33..1,560,263.20 rows=40,570 width=93) (actual time=20,474.489..20,566.485 rows=6 loops=1)

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

CTE similar_names

3. 19.732 19.732 ↑ 4.3 234 1

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

  • Filter: ((similar_name)::text !~~* 'Ellis'::text)
  • Rows Removed by Filter: 1
4. 0.306 127.743 ↑ 9,043.4 108 1

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

5. 19.797 19.797 ↑ 4.3 234 1

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

6. 0.936 107.640 ↓ 0.0 0 234

Bitmap Heap Scan on contributions_contributors cc (cost=27.21..1,225.92 rows=977 width=42) (actual time=0.459..0.460 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
7. 106.704 106.704 ↓ 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.456..0.456 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))
8. 265.485 20,357.709 ↑ 1.0 896,950 1

Hash (cost=272,653.94..272,653.94 rows=924,434 width=23) (actual time=20,357.709..20,357.709 rows=896,950 loops=1)

  • Buckets: 262144 Batches: 8 Memory Usage: 8058kB
9. 18,749.710 20,092.224 ↑ 1.0 896,950 1

Bitmap Heap Scan on contributions_addresses ca (cost=37,255.35..272,653.94 rows=924,434 width=23) (actual time=1,355.370..20,092.224 rows=896,950 loops=1)

  • Recheck Cond: ((state IS NULL) OR (lower((state)::text) ~* 'CA'::text))
  • Rows Removed by Index Recheck: 21566794
  • Heap Blocks: exact=74660 lossy=146793
10. 0.002 1,342.514 ↓ 0.0 0 1

BitmapOr (cost=37,255.35..37,255.35 rows=929,706 width=0) (actual time=1,342.514..1,342.514 rows=0 loops=1)

11. 7.860 7.860 ↓ 1.3 193,008 1

Bitmap Index Scan on ix_contributions_addresses_state (cost=0.00..1,590.77 rows=150,591 width=0) (actual time=7.860..7.860 rows=193,008 loops=1)

  • Index Cond: (state IS NULL)
12. 1,334.652 1,334.652 ↓ 9.8 7,660,685 1

Bitmap Index Scan on ix_contributions_addresses_state_lc (cost=0.00..35,202.36 rows=779,115 width=0) (actual time=1,334.652..1,334.652 rows=7,660,685 loops=1)

  • Index Cond: (lower((state)::text) ~* 'CA'::text)