Skip site navigation (1) Skip section navigation (2)

Re: Query plan for NOT IN

From: Guy Rouillier <guyr-ml1(at)burntmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query plan for NOT IN
Date: 2009-10-07 22:47:48
Message-ID: 4ACD1A94.8080706@burntmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Craig James wrote:
> Kevin Grittner wrote:
>> Which leaves the issue open -- a flexible way to flag the *reason* (or
>> *reasons*) for the absence of a value could be a nice enhancement, if
>> someone could invent a good implementation.  Of course, one could
>> always add a column to indicate the reason for a NULL; and perhaps
>> that would be as good as any scheme to attach reason flags to NULL. 
>> You'd just have to make sure the reason column was null capable for
>> those rows where there *was* a value, which would make the reason "not
>> applicable"....
> 
> I'd argue that this is just a special case of a broader problem of 
> metadata: Data about the data.  For example, I could have a temperature, 
> 40 degrees, and an error bounds, +/- 0.25 degrees.  Nobody would think 
> twice about making these separate columns.  I don't see how this is any 
> different from a person's middle initial of NULL, plus a separate column 
> indicating "not known" versus "doesn't have one" if that distinction is 
> important.  There are many examples like this, where a simple value in 
> one column isn't sufficient, so another column contains metadata that 
> qualifies or clarifies the information.  NULL is just one such case.
> 
> But, this should probably be on an SQL discussion board, not PG 
> performance...

Most DBMSs I'm aware of use a null *byte* attached to a nullable column 
to indicate whether the column is null or not.  yes/no takes one *bit*. 
  That leaves 255 other possible values to describe the state of the 
column.  That seems preferable to adding an additional column to every 
nullable column.

But as you say, that would have to be taken up with the SQL 
standardization bodies, and not PostgreSQL.

-- 
Guy Rouillier

In response to

pgsql-performance by date

Next:From: Greg SmithDate: 2009-10-08 05:14:52
Subject: Re: dump time increase by 1h with new kernel
Previous:From: Craig JamesDate: 2009-10-07 20:27:53
Subject: Re: Query plan for NOT IN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group