pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPrepared statments: partial indexes are avoided!
5+ messages / 4 participants
[nested] [flat]
* Prepared statments: partial indexes are avoided!
@ 2011-06-20 14:01 adrien ducos <aducos@hbs-research.com>
2011-06-20 14:06 ` Re: Prepared statments: partial indexes are avoided! Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: adrien ducos @ 2011-06-20 14:01 UTC (permalink / raw)
To: pgsql-admin
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.
Adrien DUCOS
--
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Prepared statments: partial indexes are avoided!
2011-06-20 14:01 Prepared statments: partial indexes are avoided! adrien ducos <aducos@hbs-research.com>
@ 2011-06-20 14:06 ` Simon Riggs <simon@2ndQuadrant.com>
2011-06-20 14:55 ` Re: Prepared statments: partial indexes are avoided! adrien ducos <aducos@hbs-research.com>
2011-06-21 22:35 ` Re: Prepared statments: partial indexes are avoided! bricklen <bricklen@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Simon Riggs @ 2011-06-20 14:06 UTC (permalink / raw)
To: adrien ducos <aducos@hbs-research.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Prepared statments: partial indexes are avoided!
2011-06-20 14:01 Prepared statments: partial indexes are avoided! adrien ducos <aducos@hbs-research.com>
2011-06-20 14:06 ` Re: Prepared statments: partial indexes are avoided! Simon Riggs <simon@2ndQuadrant.com>
@ 2011-06-20 14:55 ` adrien ducos <aducos@hbs-research.com>
2011-06-21 03:25 ` Re: Prepared statments: partial indexes are avoided! Greg Smith <greg@2ndQuadrant.com>
1 sibling, 1 reply; 5+ messages in thread
From: adrien ducos @ 2011-06-20 14:55 UTC (permalink / raw)
To: Simon Riggs <simon@2ndQuadrant.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Prepared statments: partial indexes are avoided!
2011-06-20 14:01 Prepared statments: partial indexes are avoided! adrien ducos <aducos@hbs-research.com>
2011-06-20 14:06 ` Re: Prepared statments: partial indexes are avoided! Simon Riggs <simon@2ndQuadrant.com>
2011-06-20 14:55 ` Re: Prepared statments: partial indexes are avoided! adrien ducos <aducos@hbs-research.com>
@ 2011-06-21 03:25 ` Greg Smith <greg@2ndQuadrant.com>
0 siblings, 0 replies; 5+ messages in thread
From: Greg Smith @ 2011-06-21 03:25 UTC (permalink / raw)
To: pgsql-admin
On 06/20/2011 10:55 AM, adrien ducos wrote:
> 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.
It's documented in the "Notes" section of
http://www.postgresql.org/docs/9.0/static/sql-prepare.html because it's
not specific to partial indexes. Prepared statements are very risky
when you run them against queries with a very skewed distribution, like
your case where 99% of the values for a column are the same. They help
to prevent SQL injection and can reduce parsing/planning overhead, but
prepared statements can have terrible performance in some unfortunately
common situations. Just recently I had one clients end up switching to
another ORM specifically because they couldn't get the one they started
with to stop using prepared statements, and those gave them terrible
query plans.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Prepared statments: partial indexes are avoided!
2011-06-20 14:01 Prepared statments: partial indexes are avoided! adrien ducos <aducos@hbs-research.com>
2011-06-20 14:06 ` Re: Prepared statments: partial indexes are avoided! Simon Riggs <simon@2ndQuadrant.com>
@ 2011-06-21 22:35 ` bricklen <bricklen@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: bricklen @ 2011-06-21 22:35 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-admin
On Mon, Jun 20, 2011 at 7:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> 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
>
I haven't used Oracle in years, but I seem to recall that they had
"bind variable peeking" to work around this type of problem. That
would be interesting to have in PG.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-06-21 22:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-20 14:01 Prepared statments: partial indexes are avoided! adrien ducos <aducos@hbs-research.com>
2011-06-20 14:06 ` Simon Riggs <simon@2ndQuadrant.com>
2011-06-20 14:55 ` adrien ducos <aducos@hbs-research.com>
2011-06-21 03:25 ` Greg Smith <greg@2ndQuadrant.com>
2011-06-21 22:35 ` bricklen <bricklen@gmail.com>
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