From: Leif B. Kristensen <leif@solumslekt.org>
To: Pgsql-sql@postgresql.org <Pgsql-sql@postgresql.org>
Subject: Re: Use of partial index
Date: Wed, 5 Oct 2005 19:20:34 +0200
Message-ID: <200510051920.34940.leif@solumslekt.org> (raw)
In-Reply-To: <20345.1128530948@sss.pgh.pa.us>
References: <200510051717.24390.leif@solumslekt.org>
<20345.1128530948@sss.pgh.pa.us>
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=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_born2 on 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: leif@solumslekt.org
Subject: Re: Use of partial index
In-Reply-To: <200510051920.34940.leif@solumslekt.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox