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 =:->
signature.asc
Description: OpenPGP digital signature
