What's faster: value of 0 or NULL with index

On Mon, Dec 11, 2000 at 04:28:24AM +0100, some SMTP stream spewed forth:

Hi,

I'm thinking about, what might be faster on SELECTs: a column with index
which is NOT NULL and takes the value of 0 or a column which can take
the NULL value instead of 0, also with index.

My feeling sais, that 0 and NOT NULL should be a lot more faster, but
perhaps it's not true?

If your SELECT uses the index on the NOT NULL column, then yes, the
indexed 0 should be faster. I think it takes less space as well.(?)

gh

GH <grasshacker@over-yonder.net> writes:

On Mon, Dec 11, 2000 at 04:28:24AM +0100, some SMTP stream spewed forth:

My feeling sais, that 0 and NOT NULL should be a lot more faster, but
perhaps it's not true?

If your SELECT uses the index on the NOT NULL column, then yes, the
indexed 0 should be faster. I think it takes less space as well.(?)

No, a NULL index entry should be a little smaller than a non-null one.
I doubt you could measure any speed difference, but if there were any
it'd probably be in favor of the solution with NULLs.

Also, if you expect to have a lot of these dummy entries, then it's a
good idea to represent them as NULL rather than a real value, because
the NULLs won't skew the planner's statistics about the column's most
common value.

BUT: currently, a query like "WHERE foo = 0" can use an index, whereas
the planner does not consider an index for query like "WHERE foo IS NULL".
So if you intend to actually search for the dummy entries, and you need
that to be fast, you'd have to use 0.

regards, tom lane