From: T E Schmitz <mailreg@numerixtechnology.de>
To: Greg Stark <gsstark@mit.edu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER BY and NULLs
Date: Sun, 19 Sep 2004 21:22:23 +0100
Message-ID: <414DEA7F.6050100@numerixtechnology.de> (raw)
In-Reply-To: <87zn3mjeaw.fsf@stark.xeocode.com>
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>
Hello Greg,
Greg Stark wrote:
> T E Schmitz <mailreg@numerixtechnology.de> writes:
>
>>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?
PostgreSQL by Korry Douglas + Susan Douglas, ISBN 0-7357-1257-3; Feb 2003
> 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.
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."
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mailreg@numerixtechnology.de, gsstark@mit.edu, tgl@sss.pgh.pa.us
Subject: Re: ORDER BY and NULLs
In-Reply-To: <414DEA7F.6050100@numerixtechnology.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox