explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XsUH

Settings
# exclusive inclusive rows x rows loops node
1. 528.790 728.806 ↑ 1,381.4 7 1

Nested Loop (cost=1,832.14..101,078.78 rows=9,670 width=93) (actual time=324.924..728.806 rows=7 loops=1)

  • Output: cc.id, cc.first_name, cc.last_name, cc.original_name, ca.state, ca.city, ca.zip, 'state'::text, 2
  • Join Filter: ((cc.original_name)::text ~* (('\y'::text || (sn1.name)::text) || '\y'::text))
  • Rows Removed by Join Filter: 357077
2.          

CTE similar_names2

3. 0.053 0.053 ↑ 5.8 234 1

Seq Scan on pg_temp_30.similar_names (cost=0.00..23.60 rows=1,360 width=32) (actual time=0.006..0.053 rows=234 loops=1)

  • Output: similar_names.name
4. 0.180 0.180 ↑ 5.8 234 1

CTE Scan on similar_names2 sn1 (cost=0.00..27.20 rows=1,360 width=32) (actual time=0.007..0.180 rows=234 loops=1)

  • Output: sn1.name
5. 19.085 199.836 ↓ 1.1 1,526 234

Materialize (cost=1,808.54..62,353.13 rows=1,422 width=57) (actual time=0.343..0.854 rows=1,526 loops=234)

  • Output: cc.id, cc.first_name, cc.last_name, cc.original_name, ca.state, ca.city, ca.zip
6. 6.826 180.751 ↓ 1.1 1,526 1

Gather (cost=1,808.54..62,346.02 rows=1,422 width=57) (actual time=80.197..180.751 rows=1,526 loops=1)

  • Output: cc.id, cc.first_name, cc.last_name, cc.original_name, ca.state, ca.city, ca.zip
  • Workers Planned: 4
  • Workers Launched: 4
7. 47.225 173.925 ↑ 1.2 305 5

Nested Loop Left Join (cost=808.54..61,203.82 rows=356 width=57) (actual time=73.178..173.925 rows=305 loops=5)

  • Output: cc.id, cc.first_name, cc.last_name, cc.original_name, ca.state, ca.city, ca.zip
  • Inner Unique: true
  • Filter: ((ca.state IS NULL) OR (lower((ca.state)::text) = 'ca'::text))
  • Rows Removed by Filter: 2558
  • Worker 0: actual time=68.278..171.752 rows=329 loops=1
  • Worker 1: actual time=78.109..170.169 rows=338 loops=1
  • Worker 2: actual time=68.091..173.569 rows=303 loops=1
  • Worker 3: actual time=71.510..174.882 rows=334 loops=1
8. 55.918 126.685 ↑ 3.3 2,863 5

Parallel Bitmap Heap Scan on public.contributions_contributors cc (cost=808.10..39,353.24 rows=9,390 width=42) (actual time=68.479..126.685 rows=2,863 loops=5)

  • Output: cc.id, cc.first_name, cc.last_name, cc.middle_name, cc.suffix_name, cc.original_name, cc.name_parsed, cc.occupation, cc.employer, cc.is_individual, cc.address_id
  • Recheck Cond: ((cc.original_name)::text ~* '\yEllis\y'::text)
  • Rows Removed by Index Recheck: 1596
  • Heap Blocks: exact=3883
  • Worker 0: actual time=66.248..123.577 rows=2845 loops=1
  • Worker 1: actual time=64.617..122.580 rows=3011 loops=1
  • Worker 2: actual time=67.859..126.804 rows=2770 loops=1
  • Worker 3: actual time=69.360..126.732 rows=2884 loops=1
9. 70.767 70.767 ↑ 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=70.767..70.767 rows=22,299 loops=1)

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

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

  • Output: ca.id, ca.country, ca.city, ca.state, ca.address, ca.zip, ca.original_address, ca.address_parsed
  • Index Cond: (cc.address_id = ca.id)
  • Worker 0: actual time=0.016..0.016 rows=1 loops=2845
  • Worker 1: actual time=0.015..0.015 rows=1 loops=3011
  • Worker 2: actual time=0.016..0.016 rows=1 loops=2770
  • Worker 3: actual time=0.016..0.016 rows=1 loops=2884
Planning time : 1.803 ms
Execution time : 729.481 ms