explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j9yv : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #xgHE - adds index on vendor_member.member_id; plan #WjD7; plan #Up22; plan #DYvS; plan #Uxnd; plan #bt9D; plan #cq1d; plan #riv; plan #GGAg

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.005 692.785 ↑ 35.0 1 1

Nested Loop (cost=13,397.85..16,156.03 rows=35 width=4) (actual time=658.511..692.785 rows=1 loops=1)

2. 0.015 0.015 ↑ 1.0 1 1

Index Only Scan using uq_tenant_tenant_id_wex_key on tenant (cost=0.14..1.16 rows=1 width=4) (actual time=0.009..0.015 rows=1 loops=1)

  • Index Cond: (tenant_id = 40)
  • Heap Fetches: 1
3. 503.013 692.765 ↑ 35.0 1 1

Hash Right Join (cost=13,397.70..16,154.52 rows=35 width=8) (actual time=658.499..692.765 rows=1 loops=1)

  • Hash Cond: (vendor_member.member_id = member.id)
  • Filter: (((member.email)::text ~~* '%Elizabeth Aaron%'::text) OR ((((member.first_name)::text || ' '::text) || (member.last_name)::text) ~~* '%Elizabeth Aaron%'::text) OR ((member.first_name)::text ~~* '%Elizabeth Aaron%'::text) OR ((member.last_name)::text ~~* '%Elizabeth Aaron%'::text) OR ((member.alegeus_key)::text ~~* '%Elizabeth Aaron%'::text) OR ((member.datapath_key)::text ~~* '%Elizabeth Aaron%'::text) OR ((member.wex_key)::text ~~* '%Elizabeth Aaron%'::text) OR ((vendor_member.key)::text ~~* '%Elizabeth Aaron%'::text) OR ((member.ssn)::text ~~* '%Elizabeth Aaron%'::text))
  • Rows Removed by Filter: 146,966
4. 10.426 10.426 ↑ 1.0 110,312 1

Seq Scan on vendor_member (cost=0.00..2,035.12 rows=110,312 width=11) (actual time=0.020..10.426 rows=110,312 loops=1)

5. 51.422 179.326 ↓ 2.3 146,967 1

Hash (cost=12,613.35..12,613.35 rows=62,748 width=80) (actual time=179.325..179.326 rows=146,967 loops=1)

  • Buckets: 262,144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 14,124kB
6. 19.972 127.904 ↓ 2.3 146,967 1

Nested Loop (cost=0.71..12,613.35 rows=62,748 width=80) (actual time=0.030..127.904 rows=146,967 loops=1)

7. 1.012 1.012 ↓ 1.1 1,485 1

Index Scan using uq_employer_tenant_id_wex_key on employer (cost=0.29..171.35 rows=1,363 width=8) (actual time=0.013..1.012 rows=1,485 loops=1)

  • Index Cond: (tenant_id = 40)
  • Filter: (archived IS FALSE)
  • Rows Removed by Filter: 4
8. 106.920 106.920 ↓ 1.1 99 1,485

Index Scan using member_employer_id on member (cost=0.42..8.21 rows=92 width=80) (actual time=0.004..0.072 rows=99 loops=1,485)

  • Index Cond: (employer_id = employer.employer_id)
  • Filter: ((deleted IS FALSE) AND (archived IS FALSE))
  • Rows Removed by Filter: 0