Use of partial index - Mailing list pgsql-sql
From | Leif B. Kristensen |
---|---|
Subject | Use of partial index |
Date | |
Msg-id | 200510051717.24390.leif@solumslekt.org Whole thread Raw |
Responses |
Re: Use of partial index
Re: Use of partial index |
List | pgsql-sql |
I'm a little confused about partial indexes. I have a couple of tables, like this: CREATE TABLE events ( event_id INTEGER PRIMARY KEY, tag_type_fk INTEGER REFERENCES tag_types (tag_type_id), place_fk INTEGER REFERENCES places (place_id), event_date CHAR(18) NOT NULL DEFAULT '000000003000000001', sort_date DATE NOT NULL DEFAULT '40041024BC', event_text TEXT NOT NULL DEFAULT '', sentence TEXT NOT NULL DEFAULT'' ); To this table I have created a partial index: CREATE INDEX events_born ON events (tag_type_fk) WHERE tag_type_fk = 2; Another table: CREATE TABLE participants ( -- the TMG 'E' file participant_id INTEGER PRIMARY KEY, person_fk INTEGERREFERENCES persons (person_id), event_fk INTEGER REFERENCES events (event_id), role_type_fk INTEGER REFERENCES role_types (role_type_id), is_principal BOOLEAN NOT NULL DEFAULT 'f', is_primary_event BOOLEANNOT NULL DEFAULT 'f', participant_note TEXT NOT NULL DEFAULT '', participant_name TEXT NOT NULL DEFAULT'', age_mean INTEGER NOT NULL DEFAULT 0, age_devi INTEGER NOT NULL DEFAULT 0, CONSTRAINTperson_event UNIQUE (person_id, event_id) ); And a view: CREATE OR REPLACE VIEW principals AS SELECT participants.person_fk AS person, events.event_id AS event, events.place_fk AS place, events.event_date ASevent_date, events.sort_date AS sort_date, events.tag_type_fk AS tag_type FROM events, participants WHERE events.event_id = participants.event_fk AND participants.is_principal IS TRUE; Now, here's an "explain select": pgslekt=> explain select event_date, place from principals where person=2 and tag_type=2; QUERY PLAN -----------------------------------------------------------------------Nested Loop (cost=0.00..23.15 rows=2 width=26) -> Index Scan using person_event on participants (cost=0.00..13.63 rows=3 width=4) Index Cond:(person_fk = 2) Filter: (is_principal IS TRUE) -> Index Scan using events_pkey on events (cost=0.00..3.16 rows=1 width=30) Index Cond: (events.event_id = "outer".event_fk) Filter: (tag_type_fk= 2) (7 rader) Why doesn't this SELECT use the partial index "events_born" above? Is there any way to make this happen? -- Leif Biberg Kristensen http://solumslekt.org/