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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
