From: Andreas Seltenreich <andreas+pg@gate450.dyndns.org>
To: Guido Neitzer <guido.neitzer@pharmaline.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index not used with prepared statement
Date: Sun, 11 Sep 2005 11:03:23 +0200
Message-ID: <87vf18x9gk.fsf@gate450.dyndns.org> (raw)
In-Reply-To: <33A6D7B7-FDF3-456E-9154-402E9C5BC5D9@pharmaline.de>
References: <33A6D7B7-FDF3-456E-9154-402E9C5BC5D9@pharmaline.de>
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
--
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-performance@postgresql.org
Cc: andreas+pg@gate450.dyndns.org, guido.neitzer@pharmaline.de
Subject: Re: Index not used with prepared statement
In-Reply-To: <87vf18x9gk.fsf@gate450.dyndns.org>
* 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