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