Re: Index use difference betweer LIKE, LIKE ANY?

On Tue, Mar 15, 2011 at 8:30 AM, Chetan Suttraway
<chetan(dot)suttraway(at)enterprisedb(dot)com> 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.

Please add your patch here:

https://commitfest.postgresql.org/action/commitfest_view/open

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company