pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex not used with prepared statement
3+ messages / 2 participants
[nested] [flat]
* Index not used with prepared statement
@ 2005-09-11 08:29 Guido Neitzer <guido.neitzer@pharmaline.de>
0 siblings, 1 reply; 3+ messages in thread
From: Guido Neitzer @ 2005-09-11 08:29 UTC (permalink / raw)
To: pgsql-performance
Hi.
I have a performance problem with prepared statements (JDBC prepared
statement).
This query:
PreparedStatement st = conn.prepareStatement("SELECT id FROM
dga_dienstleister WHERE plz like '45257'");
does use an index.
This query:
String plz = "45257";
PreparedStatement st = conn.prepareStatement("SELECT id FROM
dga_dienstleister WHERE plz like ?");
st.setString(1, plz);
does NOT use an index.
As it should in the end result in absolutely the same statement, the
index should be used all the time. I have to set the
protocolVersion=2 and use the JDBC2 driver to get it working (but
then the statements are created like in the first query, so no
solution, only a workaround).
I'm not sure whether this is a bug (I think it is) or a problem of
understanding.
Known problem?
I have tried PG 8.0.1, 8.0.3, 8.1beta with the JDBC-drivers
- postgresql-8.0-312.jdbc2.jar --> okay with protocolVersion=2 in the
URL
- postgresql-8.0-312.jdbc3.jar --> not okay whatever I do
I'm on Mac OS X, if that matters.
cug
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index not used with prepared statement
@ 2005-09-11 09:03 Andreas Seltenreich <andreas+pg@gate450.dyndns.org>
parent: Guido Neitzer <guido.neitzer@pharmaline.de>
0 siblings, 1 reply; 3+ messages in thread
From: Andreas Seltenreich @ 2005-09-11 09:03 UTC (permalink / raw)
To: Guido Neitzer <guido.neitzer@pharmaline.de>; +Cc: pgsql-performance
Guido Neitzer schrob:
> I have a performance problem with prepared statements (JDBC prepared
> statement).
>
> This query:
>
> PreparedStatement st = conn.prepareStatement("SELECT id FROM
> dga_dienstleister WHERE plz like '45257'");
>
> does use an index.
>
> This query:
>
> String plz = "45257";
> PreparedStatement st = conn.prepareStatement("SELECT id FROM
> dga_dienstleister WHERE plz like ?");
> st.setString(1, plz);
>
> does NOT use an index.
>
> As it should in the end result in absolutely the same statement, the
> index should be used all the time.
I'm not perfectly sure, but since the index could only be used with a
subset of all possible parameters (the pattern for like has to be
left-anchored), I could imagine the planner has to avoid the index in
order to produce an universal plan (the thing behind a prepared
statement).
Is there a reason you are using the like operator at all? IMO using
the =-operator instead in your example should produce an "index-using
prepared statement".
HTH
Andreas
--
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index not used with prepared statement
@ 2005-09-11 10:35 Guido Neitzer <guido.neitzer@pharmaline.de>
parent: Andreas Seltenreich <andreas+pg@gate450.dyndns.org>
0 siblings, 0 replies; 3+ messages in thread
From: Guido Neitzer @ 2005-09-11 10:35 UTC (permalink / raw)
To: Andreas Seltenreich <andreas+pg@gate450.dyndns.org>; pgsql-performance
On 11.09.2005, at 11:03 Uhr, Andreas Seltenreich wrote:
> I'm not perfectly sure, but since the index could only be used with a
> subset of all possible parameters (the pattern for like has to be
> left-anchored), I could imagine the planner has to avoid the index in
> order to produce an universal plan (the thing behind a prepared
> statement).
Hmm. Now I get it. So I have to look that my framework doesn't
produce a preparedStatement, instead build a complete statement
string. Weird.
> Is there a reason you are using the like operator at all? IMO using
> the =-operator instead in your example should produce an "index-using
> prepared statement".
Yes, you are right, but then I can't pass anything like '45%' to the
query. It will just return nothing.
I use the "like" because I build the queries on the fly and add a %
at the end where necessary.
And, to be clear: this is a minimal example, most of my queries are
generated by a framework. This was an example to test the behaviour.
Okay, I had problems with the understanding of prepared statements on
the client and the server side. What I thought was, that I get a
preparedStatement by JDBC which also inserts the values into the
string and this is executed on the server side.
cug
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-11 10:35 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-11 08:29 Index not used with prepared statement Guido Neitzer <guido.neitzer@pharmaline.de>
2005-09-11 09:03 ` Andreas Seltenreich <andreas+pg@gate450.dyndns.org>
2005-09-11 10:35 ` Guido Neitzer <guido.neitzer@pharmaline.de>
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