Re: number of rows estimation for bit-AND operation

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.