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

On Wednesday 05 October 2005 18:49, you wrote:
[Leif]
> > 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)
>
> 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...)
>
pgslekt=> create index events_born2 on events (event_id) where
tag_type_fk=2;
CREATE INDEX
pgslekt=> explain select event_date, place from principals where
person=2 and tag_type=2;                                      QUERY PLAN
----------------------------------------------------------------------------------------Nested Loop  (cost=0.00..22.88
rows=2width=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_born2on events  (cost=0.00..3.07 rows=1  
width=30)        Index Cond: (events.event_id = "outer".event_fk)        Filter: (tag_type_fk = 2)
(7 rader)

From 23.15 to 22.88 ... but now at least it used my partial index, as it
does a slightly better job. I'm starting to get it - I think.

Thank you for your explanation. With regards to optimization, it seems
that I'm still too hung up in MySQL issues. PostgreSQL seems to behave
a lot more intelligently with queries.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo-Linux/KDE