Re: Planner question - "bit" data types

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