From: Simon Riggs <simon@2ndQuadrant.com>
To: adrien ducos <aducos@hbs-research.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Prepared statments: partial indexes are avoided!
Date: Mon, 20 Jun 2011 15:06:48 +0100
Message-ID: <BANLkTimfT4OHQKb6Y7M4wQrffpWNUtPqJQ@mail.gmail.com> (raw)
In-Reply-To: <4DFF52BB.5060605@hbs-research.com>
References: <4DC17AAB.1080206@hbs-research.com>
<4DFF52BB.5060605@hbs-research.com>
On Mon, Jun 20, 2011 at 3:01 PM, adrien ducos <aducos@hbs-research.com> wrote:
> I have postgres 8.4
>
> I have a table "foo" with 16 million lines 99% of those lines have a column
> "bar" = 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 = 1;
> is not using the query (it is useless that is normal, using this index would
> be slower than not using it)
>
> the query
> select count(*) from foo where bar = 2; uses the index I have the answer in
> 20ms.
>
> With a prepared statement I have
>
> PREPARE fooplan (int) AS
> select count(*) from foo where bar = $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 = 2; it uses the new
> index :
>
> "Aggregate (cost=8.29..8.30 rows=1 width=0) (actual time=0.119..0.119
> rows=1 loops=1)"
> " -> Index Scan using index_foo_bar on foo (cost=0.00..8.29 rows=1
> width=0) (actual time=0.017..0.084 rows=63 loops=1)"
> " Index Cond: (bar = 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
> select count(*) from foo where bar = $1;
> explain analyze execute fooplan (2);
>
>
> "Aggregate (cost=627406.08..627406.09 rows=1 width=0) (actual
> time=11627.315..11627.316 rows=1 loops=1)"
> " -> Seq Scan on foo (cost=0.00..603856.80 rows=9419712 width=0) (actual
> time=7070.334..11627.266 rows=63 loops=1)"
> " Filter: (bar = $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 for
> 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 indexes
> 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.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
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-admin@postgresql.org
Cc: simon@2ndQuadrant.com, aducos@hbs-research.com
Subject: Re: Prepared statments: partial indexes are avoided!
In-Reply-To: <BANLkTimfT4OHQKb6Y7M4wQrffpWNUtPqJQ@mail.gmail.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