Re: booleans and nulls

On Sat, 2005-08-20 at 21:25, Matt L. wrote:
> Out of curiousity,
>
> 1. Does a boolean column occupy 1byte of disk whether
> or not the value is null or not?

No. Nulls are stored, one bit per, to a byte at a time. I.e. if you
have 8 null fields, they are stored in the same byte.

> 2. Is matching on IS NULL or = 0 more efficient?

Generally, =0 is EASIER to implement. This is because IS NULL is not
directly indexable. At least it wasn't in the past. 8.0 or 8.1 may
have made it so.

The reason for this is that indexing requires a list of operators, and
IS NULL isn't really an operator, but syntax, so it doesn't map to an
operator like = < > >= etc...

However, you can index on partials, so it was possible to make an index
that was like this:

create index a_dx on a (boofield) where boolfield IS NULL

and then IS NULL was indexed. But at the cost of an additional index to
maintain. OTOH, if most of the fields are not null, and the occasional
null is what you're looking for, then it's a good idea. If you've got
50/50 distribution of nulls and not nulls, indexing on nulls makes no
sense, since you'll never actually use the index because it will always
be cheaper to pull by seq scan, except in some clustered index situs.

> 3. If I ix'd columns w/ null does postgres know
> whatevers not indexed is null or would their be no
> point?

Actually, it indexes the nulls, it just has a hard time using the index
due to the above operator mapping issue.

To use the index with NULL / NOT NULL requires the above mentioned
partial index.

> I currently utilize null fields as 'not a value' has
> meaning in a program i've been working on as I don't
> want to put false in every column when i only need a
> couple with a true/false value.

That's a good idea, as it saves space as well. That's pretty much what
NULL was meant for.

> I'm not joining tables on NULLS, just filtering w/
> them.

Then look at the index / where IS (NOT) NULL solution, and let us know
how that works.