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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
