How to force PostgreSQL to use index on joined table column?

I have two index :

• player_team_id_index on player(team_id)
• team_active_index on team(active)

I want to get all players belonging to an active team.

1. Query 1

explain analyze 
select p.* 
from player p 
inner join team t on t.id = p.team_id
where t.active is true;

This query do not use my index, it does a sequential scan on player table (10 000 rows).

2. Query 2

explain analyze 
select p.*
from player p
where p.team_id in (select id from team t where t.active is true);

Same result as the first query, it does a sequential scan on player table (10 000 rows) without using player_team_id_idx index.

3. Query 3

select t.id from team t where t.active is true;

There is only one active team, the result is 1

explain analyze 
select p.*
from player p
where p.team_id in (1);

This query uses player_team_id_idx index which results in much faster execution time.

How can I force PostgreSQL to use player_team_id_idx for Query 1 and Query 2 ? (I'm using PostgreSQL 16.9)

Make sure to analyze (and possibly vacuum too), or let the autovacuum daemon do it:

ANALYZE collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.

Your indexes can be optimised but they are already useful and in my test, vacuum analyze immediately fixes the problem, equipping the planner with the stats it needs to consider the index scans: _{demo at db<>fiddle}

explain analyze 
select p.* 
from player p 
inner join team t on t.id = p.team_id
where t.active is true;

vacuum analyze team;
vacuum analyze player;

explain analyze 
select p.* 
from player p 
inner join team t on t.id = p.team_id
where t.active is true;

Consider a compound, multiple-key-column index create index on team(active,team_id);, or a partial index create index on team(id)where(active); and move t.active from where to join..on condition list:

drop index team_active_index;
create index team_active_index on team(active,id);

vacuum analyze team;

explain analyze 
select p.* 
from player p 
inner join team t on t.id = p.team_id and t.active;

Note the partial index doesn't need to use (active is TRUE) as a filter or an index scan condition, because the db knows from the predicate all tuples in this index meet that condition. This index is also smaller.

drop index team_active_index;
create unique index team_active_index on team(id)where(active);

vacuum analyze team;

explain analyze 
select p.* 
from player p 
inner join team t on t.id = p.team_id and t.active;