Summary

The frequent SELECT count(email_team_members.id) WHERE team_id = ? AND is_active = true query would benefit from a partial index to optimize lookups.

Current Indexes

The following indexes exist on email_team_members (from migration n8h9i0j1k2l3_add_database_indexes.py):

• idx_email_team_members_team_id - basic index on team_id
• idx_email_team_members_user_email - index on user_email
• idx_email_team_members_user_team_active - composite on (user_email, team_id, is_active)
• idx_email_team_members_team_role_active - composite on (team_id, role, is_active)

Problem

The get_member_count() query pattern:

SELECT count(id) FROM email_team_members 
WHERE team_id = ? AND is_active = true

While idx_email_team_members_team_role_active could be used, it includes an unused role column. A partial index would be more optimal:

• Smaller index size (only active members)
• No unused columns in the index
• Better for this specific query pattern

Proposed Fix

Add migration:

"""Add partial index for active team members count

Revision ID: xxxx
"""
from alembic import op

def upgrade():
    op.create_index(
        'idx_email_team_members_team_active',
        'email_team_members',
        ['team_id'],
        postgresql_where='is_active = true'
    )

def downgrade():
    op.drop_index('idx_email_team_members_team_active', 'email_team_members')

Or raw SQL:

CREATE INDEX idx_email_team_members_team_active
ON email_team_members(team_id)
WHERE is_active = true;

Acceptance Criteria

• Migration created and tested
• Index used by query planner (verify with EXPLAIN ANALYZE)
• No negative impact on write performance

Verification

EXPLAIN ANALYZE SELECT count(id) FROM email_team_members
WHERE team_id = 'some-uuid' AND is_active = true;

Priority

Medium - This is an optimization. The existing composite index provides some benefit, but a partial index would be more efficient for this specific query pattern.