From: Christopher Browne <cbbrowne@acm.org>
Subject: Re: index and min()
Date: Sun, 26 Oct 2003 15:52:05 -0500
Organization: cbbrowne Computing Inc
Lines: 25
Message-ID: <m3oew3oh6y.fsf@wolfe.cbbrowne.com>
References: <cone.1067198502.760123.11582.100@ratbert.ozaba.cx>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion,
	linux)
Cancel-Lock: sha1:QRdjCTw6D2rg6CTpXWncZYlb6No=
To: pgsql-sql@postgresql.org

In an attempt to throw the authorities off his trail, sarek@ozaba.cx transmitted:
> SELECT min(inserttime) FROM acc_pb; which takes about 11 seconds to
> complete.
>
> On the mssql server this takes less than 1 second.

If you have NO index on inserttime, then the best that can be done is
the sequential scan that you saw.

If there is an index on inserttime, then the PostgreSQL idiom that
provides a _massive_ speedup is the query:

 select inserttime from acc_pb order by inserttime limit 1;

In theory, it ought to be a nifty idea to have a way of automatically
transforming the min(inserttime) query into what I showed, but that is
likely to be difficult to do in general, and nobody has yet proposed
an implementation, so you'll have to do that yourself.
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/x.html
"Linux  and  other  OSS  advocates  are making  a  progressively  more
credible argument  that OSS software is  at least as robust  -- if not
more  -- than  commercial  alternatives." -  Microsoft lamenting  Open
Source Software in the "Halloween Document"