Bruce Momjian wrote:
Karl Denninger wrote:
Yes. In addition, functions that are part of expression indexes do get
their own optimizer statistics, so it does allow you to get optimizer
stats for your test without having to use booleans.
I see this documented in the 8.0 release notes:
* "ANALYZE" now collects statistics for expression indexes (Tom) Expression indexes (also called functional indexes) allow users to index not just columns but the results of expressions and function calls. With this release, the optimizer can gather and use statistics about the contents of expression indexes. This will greatly improve the quality of planning for queries in which an expression index is relevant.
Is this in our main documentation somewhere?
Interesting... declaring this:
create function ispermitted(text, integer) returns boolean as $$
select permission & $2 = permission from forum where forum.name=$1;
$$ Language SQL STABLE;
then calling it with "ispermitted(post.forum, '4')" as one of the terms
causes the query optimizer to treat it as a FILTER instead of a nested
loop, and it works as expected.
However, I don't think I can index that - right - since there are two
variables involved which are not part of the table being indexed.....
That should index fine. It is an _expression_ index so it can be pretty
complicated
It does not appear I can create an index on that (not that it appears to be necessary for decent performance)
create index forum_ispermitted on forum using btree(ispermitted(name, permission));
ERROR: functions in index expression must be marked IMMUTABLE
ticker=#
The function is of course of class STATIC.
-- Karl