A fairly obvious optimization?

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

Responses

Browse pgsql-hackers by date

  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