From: Sam Wong <sam@hellosam.net>
To: pgsql-performance@postgresql.org
Subject: Index use difference betweer LIKE, LIKE ANY?
Date: Fri, 25 Feb 2011 21:31:26 +0800
Message-ID: <01e801cbd4f0$5153d840$f3fb88c0$@hellosam.net> (raw)
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?
----
Here is my table and index:
CREATE TABLE shipment_lookup
(
shipment_id text NOT NULL,
lookup text NOT NULL
);
CREATE INDEX shipment_lookup_prefix
ONshipment_lookup
USING btree
(upper(lookup));
----
The table have 10 million rows.
The following statements use the index as expected:
select * from shipment_lookup where (UPPER(lookup) = 'SD1102228482' or
UPPER(lookup) ='ABCDEFGHIJK')
select * from shipment_lookup where (UPPER(lookup) = ANY
(ARRAY['SD1102228482','ABCDEFGHIJK']))
select * from shipment_lookup where (UPPER(lookup) LIKE 'SD1102228482%' or
UPPER(lookup) LIKE 'ABCDEFGHIJK%')
The following statement results in a full table scan (but this is what I
really want to do):
select * from shipment_lookup where (UPPER(lookup) LIKE
ANY(ARRAY['SD1102228482%', 'ABCDEFGHIJK%']))
I could rewrite the LIKE ANY(ARRAY[...]) as an LIKE .. OR .. LIKE .., but I
wonder what makes the difference?
Thanks,
Sam
----
Version Info:
Postgresql: "PostgreSQL 8.4.5, compiled by Visual C++ build 1400, 32-bit" on
Windows 2003
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: sam@hellosam.net
Subject: Re: Index use difference betweer LIKE, LIKE ANY?
In-Reply-To: <01e801cbd4f0$5153d840$f3fb88c0$@hellosam.net>
* 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