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).
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
