Re: Prepared statments: partial indexes are avoided!

On Mon, Jun 20, 2011 at 3:01 PM, adrien ducos <aducos(at)hbs-research(dot)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