From: adrien ducos <aducos@hbs-research.com>
To: Simon Riggs <simon@2ndQuadrant.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Prepared statments: partial indexes are avoided!
Date: Mon, 20 Jun 2011 16:55:24 +0200
Message-ID: <4DFF5F5C.7040109@hbs-research.com> (raw)
In-Reply-To: <BANLkTimfT4OHQKb6Y7M4wQrffpWNUtPqJQ@mail.gmail.com>
References: <4DC17AAB.1080206@hbs-research.com>
<4DFF52BB.5060605@hbs-research.com>
<BANLkTimfT4OHQKb6Y7M4wQrffpWNUtPqJQ@mail.gmail.com>
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
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: aducos@hbs-research.com, simon@2ndQuadrant.com
Subject: Re: Prepared statments: partial indexes are avoided!
In-Reply-To: <4DFF5F5C.7040109@hbs-research.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