Re: Planner question - "bit" data types

Bruce Momjian wrote:
> Alvaro Herrera wrote:
>
>> Karl Denninger escribi?:
>>
>>
>>> The individual boolean fields don't kill me and in terms of some of the
>>> application issues they're actually rather easy to code for.
>>>
>>> The problem with re-coding for them is extensibility (by those who
>>> install and administer the package); a mask leaves open lots of extra
>>> bits for "site-specific" use, where hard-coding booleans does not, and
>>> since the executable is a binary it instantly becomes a huge problem for
>>> everyone but me.
>>>
>> Did you try hiding the bitmask operations inside a function as Tom
>> suggested?
>>
>
> 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.....

-- Karl