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

GH <grasshacker(at)over-yonder(dot)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