Index use difference betweer LIKE, LIKE ANY?

Поиск
Список
Период
Сортировка
От Sam Wong
Тема Index use difference betweer LIKE, LIKE ANY?
Дата
Msg-id 01e801cbd4f0$5153d840$f3fb88c0$@hellosam.net
обсуждение исходный текст
Ответы Re: Index use difference betweer LIKE, LIKE ANY?  (Josh Berkus <josh@agliodbs.com>)
Список pgsql-performance
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


В списке pgsql-performance по дате отправления:

Предыдущее
От: "Sam Wong"
Дата:
Сообщение: Re: Perl Binding affects speed?
Следующее
От: Dave Johansen
Дата:
Сообщение: Re: Picking out the most recent row using a time stamp column