From: | Andreas Seltenreich <andreas+pg(at)gate450(dot)dyndns(dot)org> |
---|---|
To: | Guido Neitzer <guido(dot)neitzer(at)pharmaline(dot)de> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Index not used with prepared statement |
Date: | 2005-09-11 09:03:23 |
Message-ID: | 87vf18x9gk.fsf@gate450.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
--
From | Date | Subject | |
---|---|---|---|
Next Message | Guido Neitzer | 2005-09-11 10:35:09 | Re: Index not used with prepared statement |
Previous Message | Guido Neitzer | 2005-09-11 08:29:09 | Index not used with prepared statement |