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@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
