From: Tom Lane <tgl@sss.pgh.pa.us>
To: Leif B. Kristensen <leif@solumslekt.org>
Cc: Pgsql-sql@postgresql.org <Pgsql-sql@postgresql.org>
Subject: Re: Use of partial index
Date: Wed, 05 Oct 2005 12:49:08 -0400
Message-ID: <20345.1128530948@sss.pgh.pa.us> (raw)
In-Reply-To: <200510051717.24390.leif@solumslekt.org>
References: <200510051717.24390.leif@solumslekt.org>
"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
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: tgl@sss.pgh.pa.us, leif@solumslekt.org
Subject: Re: Use of partial index
In-Reply-To: <20345.1128530948@sss.pgh.pa.us>
* 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