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. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
