explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jRZO : 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: 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; plan #yBWv; plan #KLXs; plan #QuI; plan #PUeP; plan #i6YB; plan #FxH6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.542 686.640 ↑ 31,650.0 1 1

Nested Loop (cost=0.85..669,115.05 rows=31,650 width=4) (actual time=683.374..686.640 rows=1 loops=1)

2. 0.265 2.762 ↓ 1.1 1,492 1

Nested Loop (cost=0.43..187.30 rows=1,369 width=4) (actual time=0.107..2.762 rows=1,492 loops=1)

3. 0.049 0.049 ↑ 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.047..0.049 rows=1 loops=1)

  • Index Cond: (tenant_id = 40)
  • Heap Fetches: 1
4. 2.448 2.448 ↓ 1.1 1,492 1

Index Scan using uq_employer_tenant_id_wex_key on employer (cost=0.29..172.45 rows=1,369 width=8) (actual time=0.059..2.448 rows=1,492 loops=1)

  • Index Cond: (tenant_id = 40)
  • Filter: (archived IS FALSE)
  • Rows Removed by Filter: 4
5. 681.488 683.336 ↓ 0.0 0 1,492

Index Scan using member_employer_id on member (cost=0.42..488.17 rows=46 width=8) (actual time=0.457..0.458 rows=0 loops=1,492)

  • Index Cond: (employer_id = employer.employer_id)
  • Filter: ((deleted IS FALSE) AND (archived IS FALSE) AND (((email)::text ~~* '%Elizabeth Aaron%'::text) OR ((((first_name)::text || ' '::text) || (last_name)::text) ~~* '%Elizabeth Aaron%'::text) OR ((first_name)::text ~~* '%Elizabeth Aaron%'::text) OR ((last_name)::text ~~* '%Elizabeth Aaron%'::text) OR ((alegeus_key)::text ~~* '%Elizabeth Aaron%'::text) OR ((datapath_key)::text ~~* '%Elizabeth Aaron%'::text) OR ((wex_key)::text ~~* '%Elizabeth Aaron%'::text) OR ((ssn)::text ~~* '%Elizabeth Aaron%'::text) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (((first_name)::text ~~* '%Elizabeth%'::text) AND ((last_name)::text ~~* '%Aaron%'::text))))
  • Rows Removed by Filter: 99
6.          

SubPlan (for Index Scan)

7. 0.000 0.000 ↓ 0.0 0

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

8. 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)
9. 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))
10. 0.001 1.848 ↓ 0.0 0 1

Nested Loop (cost=80.66..136.41 rows=27 width=4) (actual time=1.848..1.848 rows=0 loops=1)

11. 0.003 1.847 ↓ 0.0 0 1

Bitmap Heap Scan on policy_holder policy_holder_1 (cost=80.24..95.88 rows=28 width=4) (actual time=1.847..1.847 rows=0 loops=1)

  • Recheck Cond: (((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. 0.002 1.844 ↓ 0.0 0 1

BitmapOr (cost=80.24..80.24 rows=28 width=0) (actual time=1.844..1.844 rows=0 loops=1)

13. 1.162 1.162 ↓ 0.0 0 1

Bitmap Index Scan on policy_holder_first_name_last_name_email_username_idx (cost=0.00..20.05 rows=7 width=0) (actual time=1.162..1.162 rows=0 loops=1)

  • Index Cond: ((first_name)::text ~~* '%Elizabeth Aaron%'::text)
14. 0.134 0.134 ↓ 0.0 0 1

Bitmap Index Scan on policy_holder_first_name_last_name_email_username_idx (cost=0.00..20.06 rows=8 width=0) (actual time=0.134..0.134 rows=0 loops=1)

  • Index Cond: ((last_name)::text ~~* '%Elizabeth Aaron%'::text)
15. 0.492 0.492 ↓ 0.0 0 1

Bitmap Index Scan on policy_holder_first_name_last_name_email_username_idx (cost=0.00..20.09 rows=12 width=0) (actual time=0.492..0.492 rows=0 loops=1)

  • Index Cond: ((username)::text ~~* '%Elizabeth Aaron%'::text)
16. 0.054 0.054 ↓ 0.0 0 1

Bitmap Index Scan on policy_holder_first_name_last_name_email_username_idx (cost=0.00..20.00 rows=1 width=0) (actual time=0.053..0.054 rows=0 loops=1)

  • Index Cond: ((email)::text ~~* '%Elizabeth Aaron%'::text)
17. 0.000 0.000 ↓ 0.0 0

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)
Planning time : 3.969 ms
Execution time : 686.891 ms