MIME-Version: 1.0
In-Reply-To: <4DFF52BB.5060605@hbs-research.com>
References: <4DC17AAB.1080206@hbs-research.com>
	<4DFF52BB.5060605@hbs-research.com>
Date: Mon, 20 Jun 2011 15:06:48 +0100
Message-ID: <BANLkTimfT4OHQKb6Y7M4wQrffpWNUtPqJQ@mail.gmail.com>
Subject: Re: Prepared statments: partial indexes are avoided!
From: Simon Riggs <simon@2ndQuadrant.com>
To: adrien ducos <aducos@hbs-research.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Mon, Jun 20, 2011 at 3:01 PM, adrien ducos <aducos@hbs-research.com> wro=
te:
> I have postgres 8.4
>
> I have a table "foo" with 16 million lines 99% of those lines have a colu=
mn
> "bar" =3D 1.
>
> I had an index on this table:
> CREATE INDEX index_foo_bar ON foo using btree (bar);
>
> The thing is that the query
> select count(*) from foo where bar =3D 1;
> is not using the query (it is useless that is normal, using this index wo=
uld
> be slower than not using it)
>
> the query
> select count(*) from foo where bar =3D 2; uses the index I have the answe=
r in
> 20ms.
>
> With a prepared statement I have
>
> PREPARE fooplan (int) AS
> =A0 select count(*) from foo where bar =3D $1;
> execute fooplan (2);
>
> also a few milliseconds
>
> Now in order to optimise this last request I droped the first index and
> added a new index:
>
> CREATE INDEX index_foo_bar ON foo using btree (bar) where
> created_by_user_group <> 1;
>
> since the query is only using the index in this case anyway.
>
> with the query
> explain analyze select count(*) from foo where bar =3D 2; it uses the new
> index :
>
> "Aggregate =A0(cost=3D8.29..8.30 rows=3D1 width=3D0) (actual time=3D0.119=
..0.119
> rows=3D1 loops=3D1)"
> " =A0-> =A0Index Scan using index_foo_bar on foo =A0(cost=3D0.00..8.29 ro=
ws=3D1
> width=3D0) (actual time=3D0.017..0.084 rows=3D63 loops=3D1)"
> " =A0 =A0 =A0 =A0Index Cond: (bar =3D 2)"
> "Total runtime: 0.144 ms"
>
> so great improvement from 20ms to 0.144ms
>
> and with the prepared statement... things becomes very bad:
> PREPARE fooplan (int) AS
> =A0 select count(*) from foo where bar =3D $1;
> explain analyze execute fooplan (2);
>
>
> "Aggregate =A0(cost=3D627406.08..627406.09 rows=3D1 width=3D0) (actual
> time=3D11627.315..11627.316 rows=3D1 loops=3D1)"
> " =A0-> =A0Seq Scan on foo =A0(cost=3D0.00..603856.80 rows=3D9419712 widt=
h=3D0) (actual
> time=3D7070.334..11627.266 rows=3D63 loops=3D1)"
> " =A0 =A0 =A0 =A0Filter: (bar =3D $1)"
> "Total runtime: 11627.357 ms"
>
> No index uses and the time becomes very bad.
>
> This is probably due to the fact the prepared statement could have "1" as=
 an
> input and so it avoids the index completely, but this is not very nice fo=
r
> software optimization since I am using pdo which uses prepared statements
> all the time and is unable to use all the partial indexes.
>
> The problem is I have 90 GB of indexes in the database and partial indexe=
s
> could help me to save some of this space on my server, in addition to
> improve the speed of the queries.

Unfortunately, prepared statements do act in the way you have seen.

I have a patch into 9.2 under discussion to improve upon this
situation, but don't hold your breath for that.

--=20
=A0Simon Riggs=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 http:/=
/www.2ndQuadrant.com/
=A0PostgreSQL Development, 24x7 Support, Training & Services