Re: Prepared statement not using an index

On Friday 02 September 2005 01:49, Guido Neitzer wrote:
> On 02.09.2005, at 0:52 Uhr, Oliver Jowett wrote:
> >> 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.
> >
> > The 8.0 drivers pass parameters individually to the backend (analogous
> > to using PREPARE/EXECUTE), while the 7.4 drivers do textual
> > substitution
> > into the query text. This can result in different query plans as
> > you've
> > discovered.
>
> This sounds like a bug to me. If a simple substitution of the
> placeholders with actual values ends with different query plan, my
> understanding is, that there is something broken in the query
> planner ...

Well, no. The OP has a 'foo LIKE ?' in there. If his 'actual' query is
something like 'foo LIKE bar%', the planner is able to determine that using
an index on foo would help, whereas in the parameterized form he cannot do
that, since 'foo LIKE %bar' would not be helped by that index.

In general, things like 'LIKE ?' will be killing performance anyway, for
exactly that reason.

>
> cug

JdV!!

--
--------------------------------------------------------------
Jan de Visser                     jdevisser(at)digitalfairway(dot)com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------