On August 12, 2019 at 1:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:


PG Bug reporting form <noreply@postgresql.org> writes:

-- Now try to query all record which contain "36dedd" AND "4e45f"
-- Using LIKE ALL, a seq scan is used which is very expensive
explain analyze
select * from mytable where mytext like all (array['%36dedd%','%4e45f%']);

This is not a bug. At most it's an unimplemented feature ...
one I can't get very excited about, considering how little
use there is for such queries.

regards, tom lane

Thanks for the clarification. I just stumbled about this because

select * from mytable where mytext like any (array['%36dedd%','%4e45f%'])

uses the gin index the same way as

select * from mytable where mytext like '%36dedd%' or mytext like '%4e45f%'

does.

Its not a show stopper in any way, but for me a working

select * from mytable where mytext like all (array['%36dedd%','%4e45f%'])

would be more elegant and more readable, because I am dealing with big amounts of free text columns.

Although I use full text search on those columns, I have a need to identify records where different search strings are contained (front and end masked, like '%part%'). This is because the free texts have many compound words, where I want to find words containing specific search strings.

Maybe this could be a good starting point for me for becoming involved. Could someone point me to the relevant source code area where this "LIKE ALL" handling should be implemented?!