explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qnp3k

Settings
# exclusive inclusive rows x rows loops node
1. 533.937 942.074 ↑ 1,015.7 7 1

Nested Loop (cost=821.29..155,586.35 rows=7,110 width=93) (actual time=538.176..942.074 rows=7 loops=1)

  • Join Filter: ((cc.original_name)::text ~* (('\y'::text || (sn1.name)::text) || '\y'::text))
  • Rows Removed by Join Filter: 357077
2.          

CTE similar_names

3. 18.407 18.407 ↑ 4.3 234 1

Function Scan on get_similar_names (cost=0.25..12.75 rows=1,000 width=32) (actual time=18.069..18.407 rows=234 loops=1)

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

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

5. 18.100 389.610 ↓ 1.1 1,526 234

Materialize (cost=808.54..127,117.15 rows=1,422 width=57) (actual time=0.429..1.665 rows=1,526 loops=234)

6. 8.496 371.510 ↓ 1.1 1,526 1

Nested Loop Left Join (cost=808.54..127,110.04 rows=1,422 width=57) (actual time=100.393..371.510 rows=1,526 loops=1)

  • Filter: ((ca.state IS NULL) OR (lower((ca.state)::text) = 'ca'::text))
  • Rows Removed by Filter: 12791
7. 153.283 248.478 ↑ 2.6 14,317 1

Bitmap Heap Scan on contributions_contributors cc (cost=808.10..39,705.37 rows=37,561 width=42) (actual time=99.212..248.478 rows=14,317 loops=1)

  • Recheck Cond: ((original_name)::text ~* '\yEllis\y'::text)
  • Rows Removed by Index Recheck: 7982
  • Heap Blocks: exact=20035
8. 95.195 95.195 ↑ 1.7 22,299 1

Bitmap Index Scan on ix_contributions_contributors_original_name_idx_t (cost=0.00..798.71 rows=37,561 width=0) (actual time=95.195..95.195 rows=22,299 loops=1)

  • Index Cond: ((original_name)::text ~* '\yEllis\y'::text)
9. 114.536 114.536 ↑ 1.0 1 14,317

Index Scan using contributions_addresses_pkey on contributions_addresses ca (cost=0.44..2.31 rows=1 width=23) (actual time=0.008..0.008 rows=1 loops=14,317)

  • Index Cond: (cc.address_id = id)
Planning time : 7.412 ms
Execution time : 942.229 ms