From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Matt L. <survivedsushi@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: booleans and nulls
Date: Tue, 30 Aug 2005 14:47:34 -0500
Message-ID: <1125431253.28179.112.camel@state.g2switchworks.com> (raw)
In-Reply-To: <20050821022557.21501.qmail@web35204.mail.mud.yahoo.com>
References: <20050821022557.21501.qmail@web35204.mail.mud.yahoo.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: smarlowe@g2switchworks.com, survivedsushi@yahoo.com
Subject: Re: booleans and nulls
In-Reply-To: <1125431253.28179.112.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox