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
User-Agent: KMail/1.8.1
References: <200510051717.24390.leif@solumslekt.org>
	<20345.1128530948@sss.pgh.pa.us>
In-Reply-To: <20345.1128530948@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200510051920.34940.leif@solumslekt.org>

On Wednesday 05 October 2005 18:49, you wrote:
[Leif]
> > Now, here's an "explain select":
> >
> > pgslekt=3D> explain select event_date, place from principals where=20
> > person=3D2 and tag_type=3D2;
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0QUERY PLAN
> >=20
=2D----------------------------------------------------------------------
> > =A0Nested Loop =A0(cost=3D0.00..23.15 rows=3D2 width=3D26)
> > =A0 =A0-> =A0Index Scan using person_event on participants =A0
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(cost=3D0.00..13.63 rows=
=3D3 width=3D4)
> > =A0 =A0 =A0 =A0 =A0Index Cond: (person_fk =3D 2)
> > =A0 =A0 =A0 =A0 =A0Filter: (is_principal IS TRUE)
> > =A0 =A0-> =A0Index Scan using events_pkey on events =A0
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(cost=3D0.00..3.16 rows=
=3D1 width=3D30)
> > =A0 =A0 =A0 =A0 =A0Index Cond: (events.event_id =3D "outer".event_fk)
> > =A0 =A0 =A0 =A0 =A0Filter: (tag_type_fk =3D 2)
> > (7 rader)
>=20
> Because the plan it did choose is better.=A0 events_born could only
> serve to select the rows with tag_type_fk =3D 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. =A0With this plan it's getting
> at most one row, by definition (since event_id is the primary key).
>=20
> Had you created the partial index as
>=20
> CREATE INDEX events_born
> =A0 =A0 ON events (event_id)
> =A0 =A0 WHERE tag_type_fk =3D 2;
>=20
> then it would be competitive for this query, since the index could
> effectively handle both constraints not just one. =A0(THe way you did
> define it, the actual content of the index keys is just dead weight,
> since they obviously must all be "2". =A0It'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...)
>=20
pgslekt=3D> create index events_born2 on events (event_id) where=20
tag_type_fk=3D2;
CREATE INDEX
pgslekt=3D> explain select event_date, place from principals where=20
person=3D2 and tag_type=3D2;
                                       QUERY PLAN
=2D------------------------------------------------------------------------=
=2D--------------
 Nested Loop  (cost=3D0.00..22.88 rows=3D2 width=3D26)
   ->  Index Scan using person_event on participants  (cost=3D0.00..13.63=20
rows=3D3 width=3D4)
         Index Cond: (person_fk =3D 2)
         Filter: (is_principal IS TRUE)
   ->  Index Scan using events_born2 on events  (cost=3D0.00..3.07 rows=3D1=
=20
width=3D30)
         Index Cond: (events.event_id =3D "outer".event_fk)
         Filter: (tag_type_fk =3D 2)
(7 rader)

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

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