pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Greg Stark <gsstark@mit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: mailreg@numerixtechnology.de, Greg Stark <gsstark@mit.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER BY and NULLs
Date: 19 Sep 2004 20:51:35 -0400
Message-ID: <874qltkbjc.fsf@stark.xeocode.com> (raw)
In-Reply-To: <21812.1095625829@sss.pgh.pa.us>
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>


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

view thread (16+ messages)  latest in thread

Message-ID: <874qltkbjc.fsf@stark.xeocode.com>
Permalink:  ../874qltkbjc.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/874qltkbjc.fsf@stark.xeocode.com

copy link · copy postgr.es

reply

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: gsstark@mit.edu, tgl@sss.pgh.pa.us
  Subject: Re: ORDER BY and NULLs
  In-Reply-To: <874qltkbjc.fsf@stark.xeocode.com>

* 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