Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 7C74BB5FDD7
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  6 Jun 2011 06:44:11 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 70528-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  6 Jun 2011 09:44:04 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from exprod7og126.obsmtp.com (exprod7og126.obsmtp.com [64.18.2.206])
	by mail.postgresql.org (Postfix) with ESMTP id 962A1B5FDCF
	for <pgsql-performance@postgresql.org>;
	Mon,  6 Jun 2011 06:44:03 -0300 (ADT)
Received: from mail-ew0-f43.google.com ([209.85.215.43]) (using TLSv1) by
	exprod7ob126.postini.com ([64.18.6.12]) with SMTP
	ID DSNKTeyhYhlSjGdCy/5/ZLw9YirLsMHDPOjs@postini.com;
	Mon, 06 Jun 2011 02:44:04 PDT
Received: by ewy20 with SMTP id 20so2239755ewy.30
	for <pgsql-performance@postgresql.org>;
	Mon, 06 Jun 2011 02:44:01 -0700 (PDT)
Received: by 10.213.2.70 with SMTP id 6mr1920917ebi.58.1307353439888;
	Mon, 06 Jun 2011 02:43:59 -0700 (PDT)
Received: from [192.168.1.183] (dsl-hkibrasgw2-ff7ac300-240.dhcp.inet.fi
	[88.195.122.240])
	by mx.google.com with ESMTPS id p80sm3057453eeb.11.2011.06.06.02.43.57
	(version=TLSv1/SSLv3 cipher=OTHER);
	Mon, 06 Jun 2011 02:43:58 -0700 (PDT)
Message-ID: <4DECA15B.2000809@enterprisedb.com>
Date: Mon, 06 Jun 2011 12:43:55 +0300
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI;
	rv:1.9.2.17) Gecko/20110510 Icedove/3.1.10
MIME-Version: 1.0
To: Chetan Suttraway <chetan.suttraway@enterprisedb.com>
CC: Josh Berkus <josh@agliodbs.com>, Sam Wong <sam@hellosam.net>,
	pgsql-performance@postgresql.org
Subject: Re: Index use difference betweer LIKE, LIKE ANY?
References: <01e801cbd4f0$5153d840$f3fb88c0$@hellosam.net>
	<4D696CFE.2010107@agliodbs.com>
	<AANLkTi=bQuynYJY0v9O5V0JdMqMGJELXyAJjBCatW3Dg@mail.gmail.com>
In-Reply-To: <AANLkTi=bQuynYJY0v9O5V0JdMqMGJELXyAJjBCatW3Dg@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201106/31
X-Sequence-Number: 43964

On 15.03.2011 14:30, Chetan Suttraway wrote:
> On Sun, Feb 27, 2011 at 2:43 AM, Josh Berkus<josh@agliodbs.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