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

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
=:->

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDJQr+JdeBCYSNAAMRAihxAKCnuq5S288ZgKQjUsSvrg bU5ppgqgCePJ52
tJwn/5Je02FAiB8ut83Qn8k=
=dIbd
-----END PGP SIGNATURE-----

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
>
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

-----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=2...9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEARECAAYFAkMlgdAACgkQvJuQZxSWSsjMlQCePc4dpE0BCT 3W//y/N9uolkmK
ViIAnjR1fF14KbP+cX+xV8lmdlL6Be2k
=NtXw
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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
>
>


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

It's added, just use the old protocol .

Here are the connection parameters

http://jdbc.postgresql.org/documenta...ml#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-----
>
> iEYEARECAAYFAkMlgdAACgkQvJuQZxSWSsjMlQCePc4dpE0BCT 3W//y/N9uolkmK
> ViIAnjR1fF14KbP+cX+xV8lmdlL6Be2k
> =NtXw
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings