Обсуждение: Prepared statement not using an index
Hi. I have an interesting problem with the JDBC drivers. When I use a select like this: "SELECT t0.aktiv, t0.id, t0.ist_teilnehmer, t0.nachname, t0.plz, t0.vorname FROM public.dga_dienstleister t0 WHERE t0.plz like ?::varchar(256) ESCAPE '|'" withBindings: 1:"53111"(plz)> the existing index on the plz column is not used. When I the same select with a concrete value, the index IS used. I use PostgreSQL 8.0.3 on Mac OS X and the JDBC driver 8.0-312 JDBC 3. After a lot of other things, I tried using a 7.4 driver and with this, the index is used in both cases. Why can this happen? Is there a setting I might have not seen? Something I do wrong? cug
Guido Neitzer wrote: > I have an interesting problem with the JDBC drivers. When I use a > select like this: [...] > the existing index on the plz column is not used. > > When I the same select with a concrete value, the index IS used. You should probably ask this on the pgsql-performance list. > I use PostgreSQL 8.0.3 on Mac OS X and the JDBC driver 8.0-312 JDBC 3. > > After a lot of other things, I tried using a 7.4 driver and with this, > the index is used in both cases. The 8.0 drivers pass parameters individually to the backend (analogous to using PREPARE/EXECUTE), while the 7.4 drivers do textual substitution into the query text. This can result in different query plans as you've discovered. -O
On 02.09.2005, at 0:52 Uhr, Oliver Jowett wrote: >> I use PostgreSQL 8.0.3 on Mac OS X and the JDBC driver 8.0-312 >> JDBC 3. >> >> After a lot of other things, I tried using a 7.4 driver and with >> this, >> the index is used in both cases. >> > > The 8.0 drivers pass parameters individually to the backend (analogous > to using PREPARE/EXECUTE), while the 7.4 drivers do textual > substitution > into the query text. This can result in different query plans as > you've > discovered. This sounds like a bug to me. If a simple substitution of the placeholders with actual values ends with different query plan, my understanding is, that there is something broken in the query planner ... cug
On Friday 02 September 2005 01:49, Guido Neitzer wrote: > On 02.09.2005, at 0:52 Uhr, Oliver Jowett wrote: > >> I use PostgreSQL 8.0.3 on Mac OS X and the JDBC driver 8.0-312 > >> JDBC 3. > >> > >> After a lot of other things, I tried using a 7.4 driver and with > >> this, > >> the index is used in both cases. > > > > The 8.0 drivers pass parameters individually to the backend (analogous > > to using PREPARE/EXECUTE), while the 7.4 drivers do textual > > substitution > > into the query text. This can result in different query plans as > > you've > > discovered. > > This sounds like a bug to me. If a simple substitution of the > placeholders with actual values ends with different query plan, my > understanding is, that there is something broken in the query > planner ... Well, no. The OP has a 'foo LIKE ?' in there. If his 'actual' query is something like 'foo LIKE bar%', the planner is able to determine that using an index on foo would help, whereas in the parameterized form he cannot do that, since 'foo LIKE %bar' would not be helped by that index. In general, things like 'LIKE ?' will be killing performance anyway, for exactly that reason. > > cug JdV!! -- -------------------------------------------------------------- Jan de Visser jdevisser@digitalfairway.com Baruk Khazad! Khazad ai-menu! --------------------------------------------------------------
You can actually get postgres to use an index in like %bar postgres has functional indexes so you need to create an index on reverse(col) and then use that function in the select statement. It's been a while, the details of actual implementation are sketchy, perhaps the performance list would be more appropriate. Dave On 2-Sep-05, at 8:41 AM, Jan de Visser wrote: > On Friday 02 September 2005 01:49, Guido Neitzer wrote: > >> On 02.09.2005, at 0:52 Uhr, Oliver Jowett wrote: >> >>>> I use PostgreSQL 8.0.3 on Mac OS X and the JDBC driver 8.0-312 >>>> JDBC 3. >>>> >>>> After a lot of other things, I tried using a 7.4 driver and with >>>> this, >>>> the index is used in both cases. >>>> >>> >>> The 8.0 drivers pass parameters individually to the backend >>> (analogous >>> to using PREPARE/EXECUTE), while the 7.4 drivers do textual >>> substitution >>> into the query text. This can result in different query plans as >>> you've >>> discovered. >>> >> >> This sounds like a bug to me. If a simple substitution of the >> placeholders with actual values ends with different query plan, my >> understanding is, that there is something broken in the query >> planner ... >> > > Well, no. The OP has a 'foo LIKE ?' in there. If his 'actual' query is > something like 'foo LIKE bar%', the planner is able to determine > that using > an index on foo would help, whereas in the parameterized form he > cannot do > that, since 'foo LIKE %bar' would not be helped by that index. > > In general, things like 'LIKE ?' will be killing performance > anyway, for > exactly that reason. > > >> >> cug >> > > JdV!! > > -- > -------------------------------------------------------------- > Jan de Visser jdevisser@digitalfairway.com > > Baruk Khazad! Khazad ai-menu! > -------------------------------------------------------------- > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > >