Considering this schema:

-- Table: cnx_ds_sis_bill_detl_tb
CREATE TABLE "cnx_ds_sis_bill_detl_tb" (
  "extr_stu_id" char(10), 
  "term_cyt" char(5), 
  "subcode" char(5), 
  "tran_seq" int2, 
  "crc" int8, 
  CONSTRAINT "pk_cnx_ds_sis_bill_detl_tb" UNIQUE ("extr_stu_id",
"term_cyt", "subcode", "tran_seq")
);

-- Index: pk_cnx_ds_sis_bill_detl_tb
CREATE UNIQUE INDEX pk_cnx_ds_sis_bill_detl_tb ON
cnx_ds_sis_bill_detl_tb USING btree (extr_stu_id bpchar_ops, term_cyt
bpchar_ops, subcode bpchar_ops, tran_seq int2_ops);

Here is a PSQL session, where I did some simple queries:

connxdatasync=# select count(*) from  cnx_ds_sis_bill_detl_tb;
  count
---------
 1607823
(1 row)

connxdatasync=# select min(extr_stu_id) from cnx_ds_sis_bill_detl_tb;
    min
------------
  000251681
(1 row)

connxdatasync=# select max(extr_stu_id) from cnx_ds_sis_bill_detl_tb;
    max
------------
  999999999
(1 row)


The select(min) and select(max) took as long as the table scan to find
the count.  It seems logical if a btree type index is available (such
as pk_cnx_ds_sis_bill_detl_tb) where the most significant bit of the
index is the column requested, it should be little more than a seek
first or seek last in the btree.  Obviously, it won't work with a hashed
index (which is neither here nor there).

Wed, 15 May 2002 11:23:26 -0700
"Dann Corbit" <DCorbit@connx.com>
A fairly obvious optimization?
On Wed, 2002-05-15 at 23:23, Dann Corbit wrote:
> The select(min) and select(max) took as long as the table scan to find
> the count.  It seems logical if a btree type index is available (such
> as pk_cnx_ds_sis_bill_detl_tb) where the most significant bit of the
> index is the column requested, it should be little more than a seek
> first or seek last in the btree.  Obviously, it won't work with a hashed
> index (which is neither here nor there).

The problem is postgres' extensibility -there is no hard-wired
connection between max() and b-tree indexes - you can define an
aggregate max() that returns something completely diffrent, say the
longest string length or the "best" optimisation techniqe which may or
may not be able to use an index.

------------
Hannu



16 May 2002 11:21:40 +0500
Hannu Krosing <hannu@tm.ee>
Re: A fairly obvious optimization?