In my rails application using the Postgres database, I have a table called
tw_schedules which belongs to a
scenarios table (one
scenario to many
tw_schedules). In addition to the
scenario_id foreign key (primary key "
id" column from scenarios table), it contains two more foreign keys: type_well_id (primary key "id" column from type_wells table) and tw_import_id (primary key "id" column from tw_imports table).
Each tw_schedules belonging to a particular scenario can either have a type_well_id or tw_import_id, but not both. This is an application enforced constraint with the result that type_well_id or tw_import_id can have a null value.
My question is: in order to optimize the query retrieval time, should I create two compound indexes on:
scenario_id + type_well_id
scenario_id + tw_import_id
or three individual indexes on columns:
scenario_id type_well_id tw_import_id
The reason I ask is that both
tw_import_id columns can contain