Simon Riggs a écrit :
On Mon, Jun 20, 2011 at 3:01 PM, adrien ducos <aducos@xxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxx <mailto:aducos@xxxxxxxxxxxxxxxx>
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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
