pg.ddx.io pgsql-admin@postgresql.org mailing list archive help / color / mirror / Atom feed From: adrien ducos <aducos@hbs-research.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Prepared statments: partial indexes are avoided!
Date: Mon, 20 Jun 2011 16:01:31 +0200
Message-ID: <4DFF52BB.5060605@hbs-research.com> (raw )
In-Reply-To: <4DC17AAB.1080206@hbs-research.com >
References: <4DC17AAB.1080206@hbs-research.com >
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
view thread (5+ messages) latest in thread
Message-ID: <4DFF52BB.5060605@hbs-research.com>
Permalink: ../4DFF52BB.5060605@hbs-research.com/
Also on: postgresql.org/message-id/4DFF52BB.5060605@hbs-research.com
copy link · copy postgr.es
reply 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
Subject: Re: Prepared statments: partial indexes are avoided!
In-Reply-To: <4DFF52BB.5060605@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