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
