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

"Leif B. Kristensen" <leif@solumslekt.org> writes:
> 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;

> ...

> 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?

Because the plan it did choose is better.  events_born could only serve
to select the rows with tag_type_fk = 2; assuming there's more than one
of those rows, there would be multiple fetches needed to see if any of
them have the desired event_id.  With this plan it's getting at most one
row, by definition (since event_id is the primary key).

Had you created the partial index as

CREATE INDEX events_born   ON events (event_id)   WHERE tag_type_fk = 2;

then it would be competitive for this query, since the index could
effectively handle both constraints not just one.  (THe way you did
define it, the actual content of the index keys is just dead weight,
since they obviously must all be "2".  It's often better to define
the index column(s) of a partial index as some other column than the
one involved in the index predicate...)
        regards, tom lane