BUG #15948: TRGM gin index is not be taken into account when using like all (array)

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: james(dot)inform(at)pharmapp(dot)de
Subject: BUG #15948: TRGM gin index is not be taken into account when using like all (array)
Date: 2019-08-11 23:35:06
Message-ID: 15948-8767af672d99063a@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15948
Logged by: James Inform
Email address: james(dot)inform(at)pharmapp(dot)de
PostgreSQL version: 11.5
Operating system: Mac OS X 10.13 / Ubuntu 18.04 LTS
Description:

Use the following to reproduce the problem:

create extension pg_trgm;
create table mytable(myid int8 primary key, mytext text);
insert into mytable select g,md5(g::text) from generate_series(1,100000)
g;
create index gin1 on mytable using gin(mytext gin_trgm_ops);

-- Now there is the following record existing in the table
-- myid: 7 mytext: 8f14e45fceea167a5a36dedd4bea2543

-- 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%']);

-- Seq Scan on mytable (cost=0.00..2185.00 rows=1 width=41) (actual
time=0.012..16.302 rows=1 loops=1)
-- Filter: (mytext ~~ ALL ('{%36dedd%,%4e45f%}'::text[]))
-- Rows Removed by Filter: 99999
-- Planning Time: 0.071 ms
-- Execution Time: 16.312 ms

-- Rewriting the same query with an where clause using the AND operator
works like one would expect from the LIKE ALL
explain analyze
select * from mytable where mytext like '%36dedd%' and mytext like
'%4e45f%';

-- Bitmap Heap Scan on mytable (cost=22.00..23.02 rows=1 width=41) (actual
time=0.136..0.136 rows=1 loops=1)
-- Recheck Cond: ((mytext ~~ '%36dedd%'::text) AND (mytext ~~
'%4e45f%'::text))
-- Heap Blocks: exact=1
-- -> Bitmap Index Scan on gin1 (cost=0.00..22.00 rows=1 width=0)
(actual time=0.130..0.130 rows=1 loops=1)
-- Index Cond: ((mytext ~~ '%36dedd%'::text) AND (mytext ~~
'%4e45f%'::text))
-- Planning Time: 0.084 ms
-- Execution Time: 0.155 ms

So, is this a bug or a feature? ;)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2019-08-11 23:41:28 Re: BUG #15948: TRGM gin index is not be taken into account when using like all (array)
Previous Message Tom Lane 2019-08-11 21:32:49 Re: BUG #15892: URGENT: Using an ICU collation in a primary key column breaks ILIKE query