Skip site navigation (1) Skip section navigation (2)

Re: number of rows estimation for bit-AND operation

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Slava Moudry <smoudry(at)4info(dot)net>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: number of rows estimation for bit-AND operation
Date: 2009-08-21 20:04:23
Message-ID: 603c8f070908211304h5544742dr717ad5082d980ecd@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2009-08-21 20:12:25
Subject: Re: number of rows estimation for bit-AND operation
Previous:From: Gaël Le MignotDate: 2009-08-21 13:37:35
Subject: Performance regression between 8.3 and 8.4 on heavy text indexing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group