2

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

and

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 type_well_id and tw_import_id columns can contain null values.

  • I would be good if you formatted the code as code (see my edits) or - even better - added the CREATE TABLE statements. People will read code easier this way. – ypercubeᵀᴹ Jun 15 '12 at 21:14
3

I would try making two composite partial indexes like

CREATE INDEX idx1 ON tw_schedules (scenario_id, type_well_id) WHERE type_well_id IS NOT NULL;

CREATE INDEX idx2 ON tw_schedules (scenario_id, tw_import_id) WHERE tw_import_id IS NOT NULL;

But, of course, everything depends on the selectivity of these indexes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.