To: mailreg@numerixtechnology.de
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 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>
In-Reply-To: <414DBF6D.8040500@numerixtechnology.de>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 19 Sep 2004 14:37:11 -0400
Message-ID: <87zn3mjeaw.fsf@stark.xeocode.com>
Lines: 24
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


T E Schmitz <mailreg@numerixtechnology.de> writes:

> I just dug out the PostgreSQL book again because I thought I might've garbled
> it:
> 
> Quote: "PostgreSQL will not index NULL values. Because an index will never
> include NULL values, it cannot be used to satisfy the ORDER BY clause of a
> query that returns all rows in a table."

You should just cross out that whole section. It's just flatly wrong. 

I had always assumed it was just people bringing assumptions over from Oracle
where it is true. Perhaps this book is to blame for some of the confusion.
Which book is it?

Postgres indexes NULLs. It can use them for ORDER BY clauses. 

Where it cannot use them is to satisfy "WHERE foo IS NULL" or "WHERE foo IS
NOT NULL" constraints though. That's an implementation detail, but it can be
worked around with partial indexes.

-- 
greg