On postgres 9.6 and 9.5 I have tested a structure like this
create table test (data text);
create index on test using gin(data gin_trgm_ops);
insert into test select md5(num::text) from generate_series(0,
1000000) as A(num);
analyze test;
explain select * from test where data like '%a%';
explain select * from test where data like '%abc%';
explain select * from test where data like '%abc%' and data like
'%a%'; -- Incorrect plan chosen
The final query will erroneously (in my opinion) attempt an index scan
for both clauses, on my machine this is marginally better than a
sequential scan for both clauses. However the correct and
significantly faster plan in a handful of cases including this one is
produced by this workaround found with help from #postgresql.
explain select * from test where data like '%abc%' and (data||'') like
'%a%'; -- Proper plan chosen
This causes the planner consider and then correctly pick the plan that
does an index scan for the GIN accelerated '%abc%' restriction and
then do filter on the remaining results for the '%a%' restriction.
I feel like this is potentially a question more for pgsql-hackers but
the mailing list suggests asking elsewhere before posting there and
this wasnt quite a "bug". A quick uninformed peek at the planner code
makes me think this isn't exactly trivial but from the "simplicity" of
the workaround id hope it is possible. This seems like an issue that
would affect all inverse indices or more generally any index where
multiple clauses against the same index might have different
performance characteristics that could be determined at plan time (so
only for constant restrictions).
--Kurt Kartaltepe