Re: number of rows estimation for bit-AND operation

On Thu, Aug 20, 2009 at 9:58 PM, Scott Marlowe<scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Thu, Aug 20, 2009 at 7:32 PM, Scott Marlowe<scott(dot)marlowe(at)gmail(dot)com> wrote:
>> 2009/8/20 Slava Moudry <smoudry(at)4info(dot)net>:
>>> Hi,
>>> Yes, I thought about putting the bit-flags in separate fields.
>>> Unfortunately - I expect to have quite a lot of these and space is an issue when you are dealing with billions of records in fact table, so I prefer to pack them into one int8.
>>
>> For giggles I created two test tables, one with a single int, one with
>> 8 bools, and put 100M entries in each.  The table with 8 bools took up
>> aprrox. 3560616 bytes, while the one with a single int took up approx.
>> 3544212
>>
>> I.e they're about the same.  You should really test to see if having a
>> lot of bools costs more than mangling ints around.  I'm guessing I
>> could fit a lot more bools in the test table due to alignment issues
>> than just 8.
>
> So, I made a table with 26 bool fields, and added 100M rows to it, and
> that table took up about 5906028 bytes.  So yea, the storage is
> greater for boolean fields, but only if they aren't null.  making them
> null would save a lot of space, so if null bits fit your model, then
> it might be worth looking into.  Certainly they're not so much bigger
> as to be unmanageable.

This is a clever idea. Tables with any non-null columns have a null
bitmap with 1 bit per field, followed by the actual values of the
non-null fields. So if the OP arranges to use true and null as the
values instead of true and false, and uses null for the flag value
that is most often wanted, it will pack down pretty tight.

Scott, did you check whether a toast table got created here and what
the size of it was?

...Robert