On 15.03.2011 14:30, Chetan Suttraway wrote:
On Sun, Feb 27, 2011 at 2:43 AM, Josh Berkus<josh@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxx).
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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
