explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G9p9

Settings
# exclusive inclusive rows x rows loops node
1. 3.260 50,782.989 ↓ 0.0 0 1

Hash Left Join (cost=78.19..1,496,173.53 rows=3,646 width=93) (actual time=50,782.989..50,782.989 rows=0 loops=1)

  • Hash Cond: (lower((cc.last_name)::text) = (sn2.similar_name)::text)
  • Filter: (((sn1.similar_name IS NOT NULL) AND (cc.last_name ~ '\yEllis\y'::text)) OR ((sn2.similar_name IS NOT NULL) AND (cc.first_name ~ '\yEllis\y'::text)))
  • Rows Removed by Filter: 2853
2.          

CTE sname

3. 14.255 14.255 ↑ 4.3 234 1

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

  • Filter: ((similar_name)::text !~~* 'Ellis'::text)
  • Rows Removed by Filter: 1
4. 4.718 50,779.660 ↑ 25.8 2,853 1

Hash Left Join (cost=32.94..1,472,065.22 rows=73,475 width=89) (actual time=54.400..50,779.660 rows=2,853 loops=1)

  • Hash Cond: (lower((cc.first_name)::text) = (sn1.similar_name)::text)
5. 43.887 50,760.576 ↑ 5.2 2,853 1

Nested Loop Left Join (cost=0.44..1,467,587.48 rows=14,695 width=57) (actual time=40.023..50,760.576 rows=2,853 loops=1)

  • Filter: ((ca.state IS NULL) OR (lower((ca.state)::text) = 'ca'::text))
  • Rows Removed by Filter: 22015
6. 50,418.273 50,418.273 ↑ 15.6 24,868 1

Seq Scan on contributions_contributors cc (cost=0.00..989,989.96 rows=388,196 width=42) (actual time=1.994..50,418.273 rows=24,868 loops=1)

  • Filter: ((last_name ~ '\yEllis\y'::text) OR (first_name ~ '\yEllis\y'::text))
  • Rows Removed by Filter: 38986916
7. 298.416 298.416 ↑ 1.0 1 24,868

Index Scan using contributions_addresses_pkey on contributions_addresses ca (cost=0.44..1.22 rows=1 width=23) (actual time=0.012..0.012 rows=1 loops=24,868)

  • Index Cond: (cc.address_id = id)
8. 0.049 14.366 ↑ 4.3 234 1

Hash (cost=20.00..20.00 rows=1,000 width=32) (actual time=14.366..14.366 rows=234 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
9. 14.317 14.317 ↑ 4.3 234 1

CTE Scan on sname sn1 (cost=0.00..20.00 rows=1,000 width=32) (actual time=14.068..14.317 rows=234 loops=1)

10. 0.039 0.069 ↑ 4.3 234 1

Hash (cost=20.00..20.00 rows=1,000 width=32) (actual time=0.069..0.069 rows=234 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
11. 0.030 0.030 ↑ 4.3 234 1

CTE Scan on sname sn2 (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.000..0.030 rows=234 loops=1)