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.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +