Re: Index use difference betweer LIKE, LIKE ANY?

On 15.03.2011 14:30, Chetan Suttraway wrote:
> On Sun, Feb 27, 2011 at 2:43 AM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:
>
>> On 2/25/11 5:31 AM, Sam Wong wrote:
>>> I found that "LIKE", "= ANY (...)", "LIKE .. OR LIKE .." against a text
>>> field used the index correctly, but not "LIKE ANY (...)". Would that be a
>>> bug?
>>
>> No, it would be a TODO. This is a known limitation; it needs some
>> clever code to make it work, and nobody's written it.
>>
>>
> came up with attached patch without thinking too much.
> With this patch, the explain output for the same query is as below:
>
> postgres=# explain select * from shipment_lookup where (UPPER(lookup)
> LIKE
> ANY(ARRAY['SD1102228482%', 'ABCDEFGHIJK%']))
> ;e
> QUERY
> PLAN
> -------------------------------------------------------------------------------------------------
> Seq Scan on shipment_lookup (cost=0.00..254057.36 rows=2000 width=14)
> * Filter: ((upper(lookup) ~~ 'SD1102228482%'::text) OR (upper(lookup) ~~
> 'ABCDEFGHIJK%'::text))*
> (2 rows)
>
> postgres-#
>
> The thing to be noted here is that the where clause "<pred> LIKE ANY
> ARRAY[..]"
> has been converted into
> (<pred> LIKE first_array_element) or (<pred> LIKE second_array_element) or
> ....
>
> Please pass on your inputs.

This suffers from the same multiple-evaluation issue that was recently
discovered in BETWEEN and IN expressions
(http://archives.postgresql.org/message-id/4D95B605.2020709@enterprisedb.com)
This transformation would also need to be done in the planner, after
checking that the left-hand expression is not volatile.

Also, even when safe, it's not clear that the transformation is always a
win. The left-hand expression could be expensive, in which case having
to evaluate it multiple times could hurt performance. Maybe yo

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com