explain.depesz.com

PostgreSQL's explain analyze made readable

Result: riv : 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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 45.416 3,982.654 ↑ 393.0 1 1

Gather (cost=4,420.86..24,414.44 rows=393 width=4) (actual time=3,950.725..3,982.654 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 0.117 3,937.238 ↓ 0.0 0 3 / 3

Hash Join (cost=3,420.86..23,375.14 rows=164 width=4) (actual time=3,208.851..3,937.238 rows=0 loops=3)

  • Hash Cond: (employer.tenant_id = tenant.tenant_id)
3. 0.003 3,936.945 ↓ 0.0 0 3 / 3

Nested Loop (cost=3,414.73..23,368.56 rows=164 width=8) (actual time=3,208.558..3,936.945 rows=0 loops=3)

4. 3,190.400 3,936.924 ↓ 0.0 0 3 / 3

Hash Left Join (cost=3,414.45..23,313.92 rows=179 width=8) (actual time=3,208.537..3,936.924 rows=0 loops=3)

  • Hash Cond: (member.id = vendor_member.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 (((member.first_name)::text ~~* '%Elizabeth%'::text) AND ((member.last_name)::text ~~* '%Aaron%'::text)))
  • Rows Removed by Filter: 254,180
5. 637.715 637.715 ↑ 1.3 254,147 3 / 3

Parallel Index Scan using member_employer_id on member (cost=0.42..16,939.57 rows=317,866 width=80) (actual time=0.036..637.715 rows=254,147 loops=3)

  • Filter: ((deleted IS FALSE) AND (archived IS FALSE))
  • Rows Removed by Filter: 26,762
6. 58.027 108.809 ↑ 1.0 110,312 3 / 3

Hash (cost=2,035.12..2,035.12 rows=110,312 width=11) (actual time=108.809..108.809 rows=110,312 loops=3)

  • Buckets: 131,072 Batches: 1 Memory Usage: 5,707kB
7. 50.782 50.782 ↑ 1.0 110,312 3 / 3

Seq Scan on vendor_member (cost=0.00..2,035.12 rows=110,312 width=11) (actual time=0.039..50.782 rows=110,312 loops=3)

8. 0.018 0.018 ↑ 1.0 1 1 / 3

Index Scan using employer_pkey on employer (cost=0.29..0.31 rows=1 width=8) (actual time=0.055..0.055 rows=1 loops=1)

  • Index Cond: (employer_id = member.employer_id)
  • Filter: (archived IS FALSE)
9. 0.030 0.176 ↑ 1.0 108 3 / 3

Hash (cost=4.78..4.78 rows=108 width=4) (actual time=0.176..0.176 rows=108 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
10. 0.146 0.146 ↑ 1.0 108 3 / 3

Index Only Scan using uq_tenant_tenant_id_wex_key on tenant (cost=0.14..4.78 rows=108 width=4) (actual time=0.055..0.146 rows=108 loops=3)

  • Heap Fetches: 324
Planning time : 3.654 ms
Execution time : 3,982.851 ms