On Tue, Mar 15, 2011 at 8:30 AM, Chetan Suttraway <chetan.suttraway@xxxxxxxxxxxxxxxx> 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. Please add your patch here: https://commitfest.postgresql.org/action/commitfest_view/open -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
