Re: [PERFORM] Index not used with prepared statement
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Index not used with prepared statement
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 -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Index not used with prepared statement
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly