Use of partial index

From: "Leif B(dot) Kristensen" <leif(at)solumslekt(dot)org>
To: "Pgsql-sql(at)postgresql(dot)org" <Pgsql-sql(at)postgresql(dot)org>
Subject: Use of partial index
Date: 2005-10-05 15:17:23
Message-ID: 200510051717.24390.leif@solumslekt.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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?
--
Leif Biberg Kristensen
http://solumslekt.org/

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message codeWarrior 2005-10-05 15:24:11 Re: using pg_tables and tablename in queries
Previous Message Andreas Kretschmer 2005-10-05 15:12:26 Re: BirthDay SQL Issue