explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yBWv : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: 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; plan #j9yv; plan #obNc; plan #Q829; plan #cLJV; plan #KQub; plan #4o68; plan #kqii; plan #1vQo

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.007 12,687.031 ↑ 31,392.0 1 1

Nested Loop (cost=31,579.98..35,221.70 rows=31,392 width=4) (actual time=10,404.430..12,687.031 rows=1 loops=1)

2. 1.429 1.429 ↑ 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=1.422..1.429 rows=1 loops=1)

  • Index Cond: (tenant_id = 40)
  • Heap Fetches: 1
3. 0.115 12,685.595 ↑ 31,392.0 1 1

Hash Join (cost=31,579.83..34,906.62 rows=31,392 width=8) (actual time=10,403.002..12,685.595 rows=1 loops=1)

  • Hash Cond: (member.employer_id = employer.employer_id)
4. 3,433.751 12,646.195 ↑ 381,654.0 1 1

Hash Right Join (cost=31,391.45..33,716.14 rows=381,654 width=8) (actual time=10,363.602..12,646.195 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) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (((member.first_name)::text ~~* '%Elizabeth%'::text) AND ((member.last_name)::text ~~* '%Aaron%'::text)))
  • Rows Removed by Filter: 762,539
5. 59.593 59.593 ↑ 1.0 110,312 1

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

6. 536.001 7,908.781 ↑ 1.0 762,440 1

Hash (cost=21,855.47..21,855.47 rows=762,878 width=80) (actual time=7,908.781..7,908.781 rows=762,440 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 75,918kB
7. 7,372.780 7,372.780 ↑ 1.0 762,440 1

Index Scan using member_employer_id on member (cost=0.42..21,855.47 rows=762,878 width=80) (actual time=4.092..7,372.780 rows=762,440 loops=1)

  • Filter: ((deleted IS FALSE) AND (archived IS FALSE))
  • Rows Removed by Filter: 80,287
8.          

SubPlan (for Hash Right Join)

9. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..4.68 rows=1 width=0) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Index Scan using member_policy_holder__member_id on member_policy_holder (cost=0.42..1.71 rows=2 width=4) (never executed)

  • Index Cond: (member.id = member_id)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using policy_holder_pkey on policy_holder (cost=0.42..1.45 rows=1 width=4) (never executed)

  • Index Cond: (policy_holder_id = member_policy_holder.policy_holder_id)
  • Filter: (((first_name)::text ~~* '%Elizabeth Aaron%'::text) OR ((last_name)::text ~~* '%Elizabeth Aaron%'::text) OR ((username)::text ~~* '%Elizabeth Aaron%'::text) OR ((email)::text ~~* '%Elizabeth Aaron%'::text))
12. 24.023 1,244.070 ↓ 0.0 0 1

Gather (cost=1,000.84..6,055.18 rows=26 width=4) (actual time=1,243.950..1,244.070 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 0.003 1,220.047 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.84..5,052.58 rows=11 width=4) (actual time=1,220.046..1,220.047 rows=0 loops=3)

14. 1,220.044 1,220.044 ↓ 0.0 0 3 / 3

Parallel Index Scan using policy_holder_pkey on policy_holder policy_holder_1 (cost=0.42..5,036.66 rows=11 width=4) (actual time=1,220.044..1,220.044 rows=0 loops=3)

  • Filter: (((first_name)::text ~~* '%Elizabeth Aaron%'::text) OR ((last_name)::text ~~* '%Elizabeth Aaron%'::text) OR ((username)::text ~~* '%Elizabeth Aaron%'::text) OR ((email)::text ~~* '%Elizabeth Aaron%'::text))
  • Rows Removed by Filter: 56,277
15. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using member_policy_holder__policy_holder_id on member_policy_holder member_policy_holder_1 (cost=0.42..1.44 rows=1 width=8) (never executed)

  • Index Cond: (policy_holder_id = policy_holder_1.policy_holder_id)
16. 0.900 39.285 ↓ 1.1 1,485 1

Hash (cost=171.35..171.35 rows=1,363 width=8) (actual time=39.285..39.285 rows=1,485 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 75kB
17. 38.385 38.385 ↓ 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=3.352..38.385 rows=1,485 loops=1)

  • Index Cond: (tenant_id = 40)
  • Filter: (archived IS FALSE)
  • Rows Removed by Filter: 4