Re: What's faster: value of 0 or NULL with index - Mailing list pgsql-general

From Tom Lane
Subject Re: What's faster: value of 0 or NULL with index
Date
Msg-id 7365.976550386@sss.pgh.pa.us
Whole thread Raw
In response to Re: What's faster: value of 0 or NULL with index  (GH <grasshacker@over-yonder.net>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Camm Maguire
Date:
Subject: Re: Sequences in transaction
Next
From: Peter Eisentraut
Date:
Subject: RE: Re: Re: Why PostgreSQL is not that popular as MySQL ?