explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jtsW

Settings
# exclusive inclusive rows x rows loops node
1. 92.655 1,117.074 ↑ 6,161.7 6 1

Hash Join (cost=259,033.35..1,529,150.22 rows=36,970 width=93) (actual time=1,012.244..1,117.074 rows=6 loops=1)

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

CTE similar_names

3. 21.535 21.535 ↑ 4.3 234 1

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

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

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

5. 21.609 21.609 ↑ 4.3 234 1

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

6. 1.170 110.682 ↓ 0.0 0 234

Bitmap Heap Scan on contributions_contributors cc (cost=27.21..1,225.92 rows=977 width=42) (actual time=0.472..0.473 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. 109.512 109.512 ↓ 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.468..0.468 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. 232.776 891.742 ↓ 1.1 894,490 1

Hash (cost=243,525.74..243,525.74 rows=842,452 width=23) (actual time=891.742..891.742 rows=894,490 loops=1)

  • Buckets: 262144 Batches: 8 Memory Usage: 8038kB
9. 580.019 658.966 ↓ 1.1 894,490 1

Bitmap Heap Scan on contributions_addresses ca (cost=9,365.25..243,525.74 rows=842,452 width=23) (actual time=104.676..658.966 rows=894,490 loops=1)

  • Recheck Cond: ((state IS NULL) OR (lower((state)::text) = 'ca'::text))
  • Heap Blocks: exact=125405
10. 0.002 78.947 ↓ 0.0 0 1

BitmapOr (cost=9,365.25..9,365.25 rows=847,166 width=0) (actual time=78.947..78.947 rows=0 loops=1)

11. 8.159 8.159 ↓ 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=8.159..8.159 rows=193,008 loops=1)

  • Index Cond: (state IS NULL)
12. 70.786 70.786 ↓ 1.0 701,482 1

Bitmap Index Scan on ix_contributions_addresses_state_lc_btree (cost=0.00..7,353.25 rows=696,575 width=0) (actual time=70.786..70.786 rows=701,482 loops=1)

  • Index Cond: (lower((state)::text) = 'ca'::text)