performance problem with 3-column indexes

From: czl(at)iname(dot)com (charles)
To: pgsql-hackers(at)postgresql(dot)org
Subject: performance problem with 3-column indexes
Date: 2001-11-06 23:29:31
Message-ID: 7e1335c.0111061529.7525a4d@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

the table has about 30k records.
simple select statement, by primary key, requires plenty of cpu
time when the primary key has three columns
when the primary key has two columns several times less cpu is
required (even though the contents of the table is the same.

so:
PRIMARY KEY(C_ID, C_D_ID, C_W_ID) -> PRIMARY KEY (C_ID, C_D_ID)
select cpu: 600 -> select cpu 60

----------- how can i work around it? -----------------

and no, I can't blend the two columns into one, the spec does not
allow that - so is there some tuning parameter?

P.S. The table definition:

CREATE TABLE CUSTOMER (
C_ID SMALLINT NOT NULL, /*--*/
C_D_ID SMALLINT NOT NULL, /*--*/
C_W_ID SMALLINT NOT NULL, /*--*/
C_FIRST VARCHAR(16) NOT NULL,
C_MIDDLE VARCHAR(2) NOT NULL,
C_LAST VARCHAR(16) NOT NULL,
C_STREET_1 VARCHAR(20) NOT NULL,
C_STREET_2 VARCHAR(20) NOT NULL,
C_CITY VARCHAR(20) NOT NULL,
C_STATE VARCHAR(2) NOT NULL,
C_ZIP INTEGER NOT NULL, /*--*/
C_PHONE NUMERIC(16) NOT NULL,
C_SINCE timestamp,
C_CREDIT VARCHAR(2) NOT NULL,
C_CREDIT_LIM NUMERIC(12,2) NOT NULL,
C_DISCOUNT NUMERIC(4,4) NOT NULL,
C_BALANCE NUMERIC(12,2) NOT NULL,
C_YTD_PAYMENT NUMERIC(12,2) NOT NULL,
C_PAYMENT_CNT SMALLINT NOT NULL, /*--*/
C_DELIVERY_CNT SMALLINT NOT NULL, /*--*/
C_DATA VARCHAR(500) NOT NULL,
PRIMARY KEY (C_ID, C_D_ID, C_W_ID)
);

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message g.p.ciceri 2001-11-06 23:39:28 GSL (GNU Scientific library, numerical routines) interface as a contributed module: pg-GSL.0.0.0
Previous Message Brent Verner 2001-11-06 23:07:26 Re: RelationFlushRelation() or RelationClearRelation()