explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ynZ

Settings
# exclusive inclusive rows x rows loops node
1. 13.232 33,871.132 ↑ 1.2 2,756 1

Unique (cost=1,673,113.52..1,673,311.83 rows=3,173 width=168) (actual time=33,851.579..33,871.132 rows=2,756 loops=1)

2.          

CTE similar_names

3. 19.920 19.920 ↑ 4.3 234 1

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

  • Filter: ((similar_name)::text !~~* 'Ellis'::text)
  • Rows Removed by Filter: 1
4. 380.827 33,857.900 ↓ 3.6 56,597 1

Sort (cost=1,673,100.77..1,673,140.43 rows=15,865 width=168) (actual time=33,851.577..33,857.900 rows=56,597 loops=1)

  • Sort Key: fec.name, fec.state, fec.city, fec.zip_code
  • Sort Method: external merge Disk: 3720kB
5. 28,970.525 33,477.073 ↓ 3.6 56,597 1

Nested Loop (cost=171.21..1,671,993.90 rows=15,865 width=168) (actual time=8,516.620..33,477.073 rows=56,597 loops=1)

  • Join Filter: (to_tsvector('english'::regconfig, (fec.name)::text) @@ to_tsquery('english'::regconfig, regexp_replace(replace((sn1.similar_name)::text, '&'::text, ''::text), '\s+'::text, ' & '::text, 'g'::text)))
  • Rows Removed by Join Filter: 4350559
6. 20.300 20.300 ↑ 4.3 234 1

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

7. 311.428 4,486.248 ↓ 5.9 18,834 234

Materialize (cost=171.21..22,021.83 rows=3,173 width=36) (actual time=2.951..19.172 rows=18,834 loops=234)

8. 3,501.075 4,174.820 ↓ 5.9 18,834 1

Bitmap Heap Scan on contributions_fec_contributions fec (cost=171.21..22,005.97 rows=3,173 width=36) (actual time=690.428..4,174.820 rows=18,834 loops=1)

  • Recheck Cond: ((name)::text ~* '\yellis\y'::text)
  • Rows Removed by Index Recheck: 2521338
  • Filter: ((state IS NULL) OR (lower((state)::text) = 'ca'::text))
  • Rows Removed by Filter: 124518
  • Heap Blocks: exact=65375 lossy=96230
9. 673.745 673.745 ↓ 11.0 215,893 1

Bitmap Index Scan on ix_contributions_fec_contributions_name_idx_t (cost=0.00..170.41 rows=19,642 width=0) (actual time=673.745..673.745 rows=215,893 loops=1)

  • Index Cond: ((name)::text ~* '\yellis\y'::text)
Planning time : 2.166 ms
Execution time : 33,873.892 ms