explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xgHE

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.010 4,921.339 ↑ 349,364.0 1 1

Hash Join (cost=32,031.11..36,310.96 rows=349,364 width=4) (actual time=2,550.977..4,921.339 rows=1 loops=1)

  • Hash Cond: (employer.tenant_id = tenant.tenant_id)
2. 0.041 4,921.265 ↑ 349,364.0 1 1

Hash Join (cost=32,024.98..35,351.77 rows=349,364 width=8) (actual time=2,550.904..4,921.265 rows=1 loops=1)

  • Hash Cond: (member.employer_id = employer.employer_id)
3. 3,558.530 4,908.599 ↑ 381,654.0 1 1

Hash Right Join (cost=31,391.45..33,716.14 rows=381,654 width=8) (actual time=2,538.239..4,908.599 rows=1 loops=1)

  • Hash Cond: (vendor_member.member_id = member.id)
  • Filter: (((member.email)::text ~~* '%Elizabeth Aaron%'::text) OR (concat(member.first_name, ' ', member.last_name) ~~* '%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
4. 12.526 12.526 ↑ 1.0 110,312 1

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

5. 304.314 978.418 ↑ 1.0 762,440 1

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 75,918kB
6. 674.104 674.104 ↑ 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=0.033..674.104 rows=762,440 loops=1)

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

SubPlan (for Hash Right Join)

8. 0.000 0.000 ↓ 0.0 0

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

9. 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)
10. 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))
11. 19.767 359.125 ↓ 0.0 0 1

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

  • Workers Planned: 2
  • Workers Launched: 2
12. 0.001 339.358 ↓ 0.0 0 3 / 3

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

13. 339.357 339.357 ↓ 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=339.356..339.357 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
14. 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)
15. 2.498 12.625 ↑ 1.0 15,169 1

Hash (cost=443.92..443.92 rows=15,169 width=8) (actual time=12.625..12.625 rows=15,169 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 721kB
16. 10.127 10.127 ↑ 1.0 15,169 1

Index Scan using uq_employer_tenant_id_wex_key on employer (cost=0.29..443.92 rows=15,169 width=8) (actual time=0.014..10.127 rows=15,169 loops=1)

  • Filter: (archived IS FALSE)
  • Rows Removed by Filter: 1,402
17. 0.015 0.064 ↑ 1.0 108 1

Hash (cost=4.78..4.78 rows=108 width=4) (actual time=0.064..0.064 rows=108 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
18. 0.049 0.049 ↑ 1.0 108 1

Index Only Scan using uq_tenant_tenant_id_wex_key on tenant (cost=0.14..4.78 rows=108 width=4) (actual time=0.007..0.049 rows=108 loops=1)

  • Heap Fetches: 108
Planning time : 3.503 ms
Execution time : 4,922.712 ms