Search Postgresql Archives

Fwd: Planner oversight for GIN indices?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux