Re: Use of partial index - Mailing list pgsql-sql

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.