From: | "Dann Corbit" <DCorbit(at)connx(dot)com> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | A fairly obvious optimization? |
Date: | 2002-05-15 18:23:26 |
Message-ID: | D90A5A6C612A39408103E6ECDD77B82906F452@voyager.corporate.connx.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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).
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel | 2002-05-15 18:38:20 | Re: Kerberos principal to dbuser mapping |
Previous Message | Tom Lane | 2002-05-15 14:13:17 | Re: Kerberos principal to dbuser mapping |