Given a table of stores (~10k records) and a table of schedules(~200k records), I am trying to create and index for the planner to use, but it ignores it so far.
select * from stores str
inner join schedule sch on sch.store_id = str.store_id
where sch.open is true and sch.tables > 0
create index sch_open_tables_idx on schedule(open, tables) where open is true and tables > 0
Is there a correct way of doing this?
The index you need is:
create index sch_open_tables_id on schedule(store_id)
where open and tables > 0;
store_id is probably the primary key on the stores table so there is already an index on it. If not:
create index store_id on stores(store_id);
these are 2 tables, so use 2 indices - one for each, anyway such small tables shouldn't require partial indices
EXPLAIN select * from stores str
inner join schedule sch USING (store_id)
where sch.open is true and tables > 0;
create index sch_open_idx on schedule(store_id) where open is true;
create index str_tables_idx on stores(store_id) where tables > 0;
vacuum analyze stores;
vacuum analyze schedule;
EXPLAIN select * from stores str
inner join schedule sch USING (store_id)
where sch.open is true and tables > 0;
tables belongs to stores?
opencolumn in the index (it will always betrue), butstore_idmay be helpful (as the first column in that index). -- Also, you could just usewhere sch.openin both your query and index,is trueis not necessary (but won't hurt either, nullability doesn't change things here; it just feels noise).