From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: index and min()
Date: Sun, 26 Oct 2003 15:52:05 -0500
Message-ID: <m3oew3oh6y.fsf@wolfe.cbbrowne.com> (raw)
References: <cone.1067198502.760123.11582.100@ratbert.ozaba.cx>
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"
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: cbbrowne@acm.org
Subject: Re: index and min()
In-Reply-To: <m3oew3oh6y.fsf@wolfe.cbbrowne.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