Use of partial index

Поиск
Список
Период
Сортировка
От Leif B. Kristensen
Тема Use of partial index
Дата
Msg-id 200510051717.24390.leif@solumslekt.org
обсуждение исходный текст
Ответы Re: Use of partial index
Re: Use of partial index
Список 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/


В списке pgsql-sql по дате отправления:

Предыдущее
От: Kenneth Gonsalves
Дата:
Сообщение: Re: BirthDay SQL Issue
Следующее
От: "Dmitri Bichko"
Дата:
Сообщение: Re: Use of partial index