Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:


The reason I ask is that both type_well_id and tw_import_id columns can contain null values.

share|improve this question
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
up vote 3 down vote accepted

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.

share|improve this answer
Thank you. This precisely answers my question. – Bharat Jun 19 '12 at 15:58

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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