explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 41.314 1,508.487 ↑ 140.0 1 1

Gather (cost=4,420.86..23,417.57 rows=140 width=4) (actual time=1,478.973..1,508.487 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 0.119 1,467.173 ↓ 0.0 0 3 / 3

Hash Join (cost=3,420.86..22,403.57 rows=58 width=4) (actual time=1,208.631..1,467.173 rows=0 loops=3)

  • Hash Cond: (employer.tenant_id = tenant.tenant_id)
3. 0.007 1,466.889 ↓ 0.0 0 3 / 3

Nested Loop (cost=3,414.73..22,397.28 rows=58 width=8) (actual time=1,208.347..1,466.889 rows=0 loops=3)

4. 778.178 1,466.866 ↓ 0.0 0 3 / 3

Hash Left Join (cost=3,414.45..22,377.74 rows=64 width=8) (actual time=1,208.324..1,466.866 rows=0 loops=3)

  • Hash Cond: (member.id = vendor_member.member_id)
  • Filter: (((((member.first_name)::text || ' '::text) || (member.last_name)::text) ~~* '%Elizabeth Aaron%'::text) OR ((vendor_member.key)::text ~~* '%Elizabeth Aaron%'::text))
  • Rows Removed by Filter: 254,180
5. 580.559 580.559 ↑ 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=22) (actual time=0.037..580.559 rows=254,147 loops=3)

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

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 5,707kB
7. 46.468 46.468 ↑ 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.042..46.468 rows=110,312 loops=3)

8. 0.016 0.016 ↑ 1.0 1 1 / 3

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
10. 0.137 0.137 ↑ 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.049..0.137 rows=108 loops=3)

  • Heap Fetches: 324
Planning time : 1.378 ms
Execution time : 1,508.661 ms