Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

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
add comment

1 Answer

up vote 1 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
add comment

Your Answer

 
discard

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.