From: Dmitri Bichko <dbichko@aveopharma.com>
To: Leif B. Kristensen <leif@solumslekt.org>
To: Pgsql-sql@postgresql.org
Subject: Re: Use of partial index
Date: Wed, 5 Oct 2005 12:44:56 -0400
Message-ID: <F18A6F7CF1661F46920F2CF713122FED46CCC0@mail.aveo.aveopharma.com> (raw)
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 confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer
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: dbichko@aveopharma.com, leif@solumslekt.org
Subject: Re: Use of partial index
In-Reply-To: <F18A6F7CF1661F46920F2CF713122FED46CCC0@mail.aveo.aveopharma.com>
* 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