Re: Prepared statments: partial indexes are avoided!

Simon Riggs a écrit :
> 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.
>
>
Ok, Thanks for your answer. I think this should at least be writen in
this documentation:
http://www.postgresql.org/docs/9.0/static/indexes-partial.html
as a known limitation to avoid people spending hours of search for why
is the application becoming slow.
--

Logo_HBS_mail.jpg

Adrien DUCOS
Analyste développeur
aducos@hbs-research.com <mailto:aducos@hbs-research.com>
www.hbs-research.com <http://www.hbs-research.com/>
+33 (0)9 70 44 64 10
11-15 quai du Président Paul Doumer
92400 Courbevoie