Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Use of partial index
Date: Wed, 5 Oct 2005 12:44:56 -0400
Message-ID: <F18A6F7CF1661F46920F2CF713122FED46CCC0@mail.aveo.aveopharma.com>
Thread-Topic: [SQL] Use of partial index
Thread-Index: AcXJyiBIkV6bC09IS3CjOD2MP1YXwwAAMfaA
From: "Dmitri Bichko" <dbichko@aveopharma.com>
To: "Leif B. Kristensen" <leif@solumslekt.org>,
	<Pgsql-sql@postgresql.org>

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 =3D 2' is going to=

be a lot more selective (ie return less rows) than 'tag_type_fk =3D 2', s=
o
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=20
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Leif B.=20
> Kristensen
> Sent: Wednesday, October 05, 2005 11:17 AM
> To: Pgsql-sql@postgresql.org
> Subject: [SQL] Use of partial index
>=20
>=20
> I'm a little confused about partial indexes. I have a couple=20
> of tables,=20
> like this:
>=20
> 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 ''
> );
>=20
> To this table I have created a partial index:
>=20
> CREATE INDEX events_born
>     ON events (tag_type_fk)
>     WHERE tag_type_fk =3D 2;
>=20
> Another table:
>=20
> 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)
> );
>=20
> And a view:
>=20
> 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 =3D participants.event_fk
> AND
>     participants.is_principal IS TRUE;
>=20
> Now, here's an "explain select":
>=20
> pgslekt=3D> explain select event_date, place from principals where=20
> person=3D2 and tag_type=3D2;
>                                        QUERY PLAN
> --------------------------------------------------------------
> ---------
>  Nested Loop  (cost=3D0.00..23.15 rows=3D2 width=3D26)
>    ->  Index Scan using person_event on participants =20
>                        (cost=3D0.00..13.63 rows=3D3 width=3D4)
>          Index Cond: (person_fk =3D 2)
>          Filter: (is_principal IS TRUE)
>    ->  Index Scan using events_pkey on events =20
>                        (cost=3D0.00..3.16 rows=3D1 width=3D30)
>          Index Cond: (events.event_id =3D "outer".event_fk)
>          Filter: (tag_type_fk =3D 2)
> (7 rader)
>=20
> Why doesn't this SELECT use the partial index "events_born" above? Is=20=

> there any way to make this happen?
> --=20
> Leif Biberg Kristensen
> http://solumslekt.org/
>=20
> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>=20
The information transmitted is intended only for the person or entity to =
which it is addressed and may contain confidential and/or privileged mate=
rial. Any review, retransmission, dissemination or other use of, or takin=
g 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 comput=
er