To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: mailreg@numerixtechnology.de, Greg Stark <gsstark@mit.edu>,
	pgsql-sql@postgresql.org
Subject: Re: ORDER BY and NULLs
References: <011d01c49e5b$c88fdb20$2766f30a@development.greatgulfhomes.com>
	<414DB1AB.5080207@sympatico.ca>
	<414DB594.5000300@numerixtechnology.de>
	<19733.1095613137@sss.pgh.pa.us>
	<414DBF6D.8040500@numerixtechnology.de>
	<87zn3mjeaw.fsf@stark.xeocode.com>
	<414DEA7F.6050100@numerixtechnology.de>
	<21812.1095625829@sss.pgh.pa.us>
In-Reply-To: <21812.1095625829@sss.pgh.pa.us>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 19 Sep 2004 20:51:35 -0400
Message-ID: <874qltkbjc.fsf@stark.xeocode.com>
Lines: 30
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


Tom Lane <tgl@sss.pgh.pa.us> writes:

> > The paragraph continues:
> > "If the SELECT command included the clause WHERE phone NOT NULL, 
> > PostgreSQL could use the index to satisfy the ORDER BY clause.
> > An index that covers optional (NOT NULL) columns will not be used to 
> > speed table joins either."
> 
> My goodness, it seems to be a veritable fount of misinformation :-(
> 
> I wonder how much of this is stuff that is true for Oracle and they just
> assumed it carried over?

The first part is true for Oracle. You have to add the WHERE phone NOT NULL to
convince Oracle it can use an index. Or just make the column NOT NULL to begin
with I think.

However as far as I recall the second part is not true. Oracle is smart enough
to realize that an equijoin clause implies NOT NULL and therefore allows it to
use the index.

(This may have all changed in Oracle 9+. The last I saw of Oracle was 8i)

I wonder if they just tried explain on a bunch of queries and noticed that
postgres wasn't using an index for SELECT * FROM foo ORDER BY bar and came up
with explanations for the patterns they saw?

-- 
greg