Re: Prepared statement not using an index

You can actually get postgres to use an index in like %bar

postgres has functional indexes so you need to create an index on
reverse(col) and then use that function in the select statement.

It's been a while, the details of actual implementation are sketchy,
perhaps the performance list would be more appropriate.

Dave
On 2-Sep-05, at 8:41 AM, Jan de Visser wrote:

> 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@digitalfairway.com
>
>                 Baruk Khazad! Khazad ai-menu!
> --------------------------------------------------------------
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>