explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 8.345 ↑ 55.0 1 1

Nested Loop Left Join (cost=69.59..235.89 rows=55 width=4) (actual time=8.343..8.345 rows=1 loops=1)

2. 0.017 8.267 ↑ 55.0 1 1

Hash Join (cost=69.18..158.91 rows=55 width=4) (actual time=8.265..8.267 rows=1 loops=1)

  • Hash Cond: (employer.tenant_id = tenant.tenant_id)
3. 0.010 8.063 ↑ 55.0 1 1

Nested Loop (cost=63.05..152.63 rows=55 width=8) (actual time=8.062..8.063 rows=1 loops=1)

4. 0.043 7.974 ↑ 55.0 1 1

Bitmap Heap Scan on member (cost=62.76..92.85 rows=55 width=8) (actual time=7.974..7.974 rows=1 loops=1)

  • Recheck Cond: (((email)::text ~~* '%Elizabeth Aaron%'::text) OR (((last_name)::text ~~* '%Aaron%'::text) AND ((first_name)::text ~~* '%Elizabeth%'::text)))
  • Heap Blocks: exact=1
5. 0.003 7.931 ↓ 0.0 0 1

BitmapOr (cost=62.76..62.76 rows=55 width=0) (actual time=7.931..7.931 rows=0 loops=1)

6. 2.303 2.303 ↓ 0.0 0 1

Bitmap Index Scan on member__email_gin (cost=0.00..20.41 rows=54 width=0) (actual time=2.303..2.303 rows=0 loops=1)

  • Index Cond: ((email)::text ~~* '%Elizabeth Aaron%'::text)
7. 0.008 5.625 ↓ 0.0 0 1

BitmapAnd (cost=42.09..42.09 rows=1 width=0) (actual time=5.625..5.625 rows=0 loops=1)

8. 0.498 0.498 ↑ 1.2 59 1

Bitmap Index Scan on member__last_name_gin (cost=0.00..5.55 rows=73 width=0) (actual time=0.498..0.498 rows=59 loops=1)

  • Index Cond: ((last_name)::text ~~* '%Aaron%'::text)
9. 5.119 5.119 ↓ 1.1 4,302 1

Bitmap Index Scan on member__first_name_gin (cost=0.00..36.26 rows=3,768 width=0) (actual time=5.118..5.119 rows=4,302 loops=1)

  • Index Cond: ((first_name)::text ~~* '%Elizabeth%'::text)
10. 0.079 0.079 ↑ 1.0 1 1

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

  • Index Cond: (employer_id = member.employer_id)
11. 0.016 0.187 ↑ 1.0 108 1

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

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

  • Heap Fetches: 108
13. 0.076 0.076 ↓ 0.0 0 1

Index Only Scan using vendor_member__member_id on vendor_member (cost=0.42..1.39 rows=1 width=4) (actual time=0.075..0.076 rows=0 loops=1)

  • Index Cond: (member_id = member.id)
  • Heap Fetches: 0