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).
