explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1vQo : 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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.360 1,953.818 ↑ 31,389.0 1 1

Hash Left Join (cost=3,414.88..670,414.52 rows=31,389 width=4) (actual time=1,945.972..1,953.818 rows=1 loops=1)

  • Hash Cond: (member.id = vendor_member.member_id)
2. 1.989 1,890.668 ↑ 31,389.0 1 1

Nested Loop (cost=0.85..666,841.70 rows=31,389 width=4) (actual time=1,882.823..1,890.668 rows=1 loops=1)

3. 0.846 4.214 ↓ 1.1 1,485 1

Nested Loop (cost=0.43..186.14 rows=1,363 width=4) (actual time=0.030..4.214 rows=1,485 loops=1)

4. 0.011 0.011 ↑ 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.008..0.011 rows=1 loops=1)

  • Index Cond: (tenant_id = 40)
  • Heap Fetches: 1
5. 3.357 3.357 ↓ 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.020..3.357 rows=1,485 loops=1)

  • Index Cond: (tenant_id = 40)
  • Filter: (archived IS FALSE)
  • Rows Removed by Filter: 4
6. 1,443.567 1,884.465 ↓ 0.0 0 1,485

Index Scan using member_employer_id on member (cost=0.42..488.65 rows=46 width=8) (actual time=1.268..1.269 rows=0 loops=1,485)

  • 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
7.          

SubPlan (for Index Scan)

8. 140.276 440.898 ↓ 0.0 0 146,966

Nested Loop (cost=0.84..4.68 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=146,966)

9. 293.932 293.932 ↓ 0.0 0 146,966

Index Scan using member_policy_holder__member_id on member_policy_holder (cost=0.42..1.71 rows=2 width=4) (actual time=0.002..0.002 rows=0 loops=146,966)

  • Index Cond: (member.id = member_id)
10. 6.690 6.690 ↓ 0.0 0 446

Index Scan using policy_holder_pkey on policy_holder (cost=0.42..1.45 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=446)

  • 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))
  • Rows Removed by Filter: 1
11. 0.000 0.000 ↓ 0.0 0

Gather (cost=1,000.84..6,055.18 rows=26 width=4) (never executed)

  • Workers Planned: 2
  • Workers Launched: 0
12. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..5,052.58 rows=11 width=4) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Parallel Index Scan using policy_holder_pkey on policy_holder policy_holder_1 (cost=0.42..5,036.66 rows=11 width=4) (never executed)

  • 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))
14. 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)
15. 33.946 62.790 ↑ 1.0 110,312 1

Hash (cost=2,035.12..2,035.12 rows=110,312 width=4) (actual time=62.789..62.790 rows=110,312 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,903kB
16. 28.844 28.844 ↑ 1.0 110,312 1

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

Planning time : 2.796 ms
Execution time : 1,954.075 ms