pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPrepared statement not using index
7+ messages / 4 participants
[nested] [flat]
* Prepared statement not using index
@ 2005-09-01 23:09 Guido Neitzer <guido.neitzer@pharmaline.de>
0 siblings, 2 replies; 7+ messages in thread
From: Guido Neitzer @ 2005-09-01 23:09 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Prepared statement not using index
@ 2005-09-12 04:58 John A Meinel <john@arbash-meinel.com>
parent: Guido Neitzer <guido.neitzer@pharmaline.de>
1 sibling, 0 replies; 7+ messages in thread
From: John A Meinel @ 2005-09-12 04:58 UTC (permalink / raw)
To: Guido Neitzer <guido.neitzer@pharmaline.de>; +Cc: pgsql-performance
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
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Prepared statement not using index
@ 2005-09-12 12:38 Dave Cramer <pg@fastcrypt.com>
parent: Guido Neitzer <guido.neitzer@pharmaline.de>
1 sibling, 2 replies; 7+ messages in thread
From: Dave Cramer @ 2005-09-12 12:38 UTC (permalink / raw)
To: Guido Neitzer <guido.neitzer@pharmaline.de>; +Cc: pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Prepared statement not using index
@ 2005-09-12 13:22 Guido Neitzer <guido.neitzer@pharmaline.de>
parent: Dave Cramer <pg@fastcrypt.com>
1 sibling, 1 reply; 7+ messages in thread
From: Guido Neitzer @ 2005-09-12 13:22 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Prepared statement not using index
@ 2005-09-12 13:26 Greg Sabino Mullane <greg@turnstep.com>
parent: Dave Cramer <pg@fastcrypt.com>
1 sibling, 1 reply; 7+ messages in thread
From: Greg Sabino Mullane @ 2005-09-12 13:26 UTC (permalink / raw)
To: pgsql-performance
-----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-----
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Prepared statement not using index
@ 2005-09-12 15:28 Dave Cramer <pg@fastcrypt.com>
parent: Guido Neitzer <guido.neitzer@pharmaline.de>
0 siblings, 0 replies; 7+ messages in thread
From: Dave Cramer @ 2005-09-12 15:28 UTC (permalink / raw)
To: Guido Neitzer <guido.neitzer@pharmaline.de>; +Cc: pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Prepared statement not using index
@ 2005-09-12 15:30 Dave Cramer <pg@fastcrypt.com>
parent: Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 0 replies; 7+ messages in thread
From: Dave Cramer @ 2005-09-12 15:30 UTC (permalink / raw)
To: Greg Sabino Mullane <greg@turnstep.com>; +Cc: pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-09-12 15:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-01 23:09 Prepared statement not using index Guido Neitzer <guido.neitzer@pharmaline.de>
2005-09-12 04:58 ` John A Meinel <john@arbash-meinel.com>
2005-09-12 12:38 ` Dave Cramer <pg@fastcrypt.com>
2005-09-12 13:22 ` Guido Neitzer <guido.neitzer@pharmaline.de>
2005-09-12 15:28 ` Dave Cramer <pg@fastcrypt.com>
2005-09-12 13:26 ` Greg Sabino Mullane <greg@turnstep.com>
2005-09-12 15:30 ` Dave Cramer <pg@fastcrypt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox