From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
To: Chetan Suttraway <chetan.suttraway@enterprisedb.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Sam Wong <sam@hellosam.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index use difference betweer LIKE, LIKE ANY?
Date: Mon, 06 Jun 2011 12:43:55 +0300
Message-ID: <4DECA15B.2000809@enterprisedb.com> (raw)
In-Reply-To: <AANLkTi=bQuynYJY0v9O5V0JdMqMGJELXyAJjBCatW3Dg@mail.gmail.com>
References: <01e801cbd4f0$5153d840$f3fb88c0$@hellosam.net>
<4D696CFE.2010107@agliodbs.com>
<AANLkTi=bQuynYJY0v9O5V0JdMqMGJELXyAJjBCatW3Dg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki.linnakangas@enterprisedb.com, chetan.suttraway@enterprisedb.com, josh@agliodbs.com, sam@hellosam.net
Subject: Re: Index use difference betweer LIKE, LIKE ANY?
In-Reply-To: <4DECA15B.2000809@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox