Thread: Prepared statement not using 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: > 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 I've had this problem in the past. In my case, the issue was that the column I was searching had a mixed blend of possible values. For example, with 1M rows, the number 3 occurred 100 times, but the number 18 occurred 700,000 times. So when I manually did a search for 3, it naturally realized that it could use an index scan, because it had the statistics to say it was very selective. If I manually did a search for 18, it switched to sequential scan, because it was not very selective (both are the correct plans). But if you create a prepared statement, parameterized on this number, postgres has no way of knowing ahead of time, whether you will be asking about 3 or 18, so when the query is prepared, it has to be pessimistic, and avoid worst case behavior, so it choses to always use a sequential scan. The only way I got around this was with writing a plpgsql function which used the EXECUTE syntax to dynamically re-plan part of the query. Hope this makes sense. This may or may not be your problem, without knowing more about you setup. But the symptoms seem similar. John =:->
Attachment
The difference between the 7.4 driver and the 8.0.3 driver is the 8.0.3 driver is using server side prepared statements and binding the parameter to the type in setXXX(n,val). The 7.4 driver just replaces the ? with the value and doesn't use server side prepared statements. Dave On 1-Sep-05, at 7:09 PM, Guido Neitzer wrote: > 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 > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >
On 12.09.2005, at 14:38 Uhr, Dave Cramer wrote: > The difference between the 7.4 driver and the 8.0.3 driver is the > 8.0.3 driver is using server side prepared statements and binding > the parameter to the type in setXXX(n,val). Would be a good idea when this were configurable. I found my solution (use the JDBC2 drivers with protocolVersion=2), but how long will this work? cug
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > The difference between the 7.4 driver and the 8.0.3 driver is the > 8.0.3 driver is using server side prepared statements and binding the > parameter to the type in setXXX(n,val). > > The 7.4 driver just replaces the ? with the value and doesn't use > server side prepared statements. DBD::Pg has a few flags that enables you to do things like purposely avoid using server side prepares, and force a reprepare of a particular statement. Perhaps something like that is available for the JDBC driver? If not, maybe someone would be willing to add it in? - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200509120925 https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEARECAAYFAkMlgdAACgkQvJuQZxSWSsjMlQCePc4dpE0BCT3W//y/N9uolkmK ViIAnjR1fF14KbP+cX+xV8lmdlL6Be2k =NtXw -----END PGP SIGNATURE-----
On 12-Sep-05, at 9:22 AM, Guido Neitzer wrote: > On 12.09.2005, at 14:38 Uhr, Dave Cramer wrote: > > >> The difference between the 7.4 driver and the 8.0.3 driver is the >> 8.0.3 driver is using server side prepared statements and binding >> the parameter to the type in setXXX(n,val). >> > > Would be a good idea when this were configurable. You found the configuration for it > > I found my solution (use the JDBC2 drivers with protocolVersion=2), > but how long will this work? I think you would be better understanding what the correct type is for the index to work properly. > > cug > >
It's added, just use the old protocol . Here are the connection parameters http://jdbc.postgresql.org/documentation/head/connect.html#connection- parameters Dave On 12-Sep-05, at 9:26 AM, Greg Sabino Mullane wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > >> The difference between the 7.4 driver and the 8.0.3 driver is the >> 8.0.3 driver is using server side prepared statements and binding the >> parameter to the type in setXXX(n,val). >> >> The 7.4 driver just replaces the ? with the value and doesn't use >> server side prepared statements. >> > > DBD::Pg has a few flags that enables you to do things like > purposely avoid > using server side prepares, and force a reprepare of a particular > statement. > Perhaps something like that is available for the JDBC driver? If not, > maybe someone would be willing to add it in? > > - -- > Greg Sabino Mullane greg@turnstep.com > PGP Key: 0x14964AC8 200509120925 > https://www.biglumber.com/x/web? > pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > > -----BEGIN PGP SIGNATURE----- > > iEYEARECAAYFAkMlgdAACgkQvJuQZxSWSsjMlQCePc4dpE0BCT3W//y/N9uolkmK > ViIAnjR1fF14KbP+cX+xV8lmdlL6Be2k > =NtXw > -----END PGP SIGNATURE----- > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >