On Wed, 5 Oct 2005, Leif B. Kristensen wrote: > 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 INTEGER REFERENCES 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 BOOLEAN NOT 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, > CONSTRAINT person_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 AS event_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?
I would think you'd want an index ON events(event_id) WHERE tag_type_fk=2 for the query given. ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match