Message-ID: <43250AFA.7010900@arbash-meinel.com>
Date: Mon, 12 Sep 2005 00:58:34 -0400
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Macintosh/20050716)
MIME-Version: 1.0
To: Guido Neitzer <guido.neitzer@pharmaline.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Prepared statement not using index
References: <08E57775-5E88-42B0-926D-21CAFB21CF24@pharmaline.de>
In-Reply-To: <08E57775-5E88-42B0-926D-21CAFB21CF24@pharmaline.de>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigAF6C9EEC5C4289FFE3491839"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigAF6C9EEC5C4289FFE3491839
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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

--------------enigAF6C9EEC5C4289FFE3491839
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

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

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

--------------enigAF6C9EEC5C4289FFE3491839--