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

As I understand it, partial indices are generally useful when you only
want to index a range of values, or if the select condition is on a
different field from the one being indexed (eg: ON foo (a) WHERE b IS
NOT NULL).

I am just guessing here, but it sounds like 'person_fk = 2' is going to
be a lot more selective (ie return less rows) than 'tag_type_fk = 2', so
it's quicker to use the pkey and then filter the results.

Depending on how many 'tag_type' values you have, indexing on it will
not help at all.  In other words, if more than a few percent of the rows
have the value '2' for 'tag_type_fg', postgres will tend to favour more
selective indices if you are doing a join, or a seqscan if you are doing
a straight select on that value.

Does that help?
Dmitri
PS Your query seems to be quite quick already, why don't you like this
plan?

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Leif B.
> Kristensen
> Sent: Wednesday, October 05, 2005 11:17 AM
> To: Pgsql-sql@postgresql.org
> Subject: [SQL] Use of partial index
>
>
> 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/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
The information transmitted is intended only for the person or entity to which it is addressed and may contain
confidentialand/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any
actionin reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you
receivedthis in error, please contact the sender and delete the material from any computer