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

6 Nov 2001 15:29:31 -0800
czl@iname.com (charles)
performance problem with 3-column indexes

Can you give an example query and explain output for both cases?
Have you run vacuum analyze?

Since I haven't seen the query, one thing that might bite you would
be if you aren't casting your constants to smallint, although I
don't know why that would change on the index definition.

On 6 Nov 2001, charles wrote:

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


Wed, 7 Nov 2001 09:26:00 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: performance problem with 3-column indexes
czl@iname.com (charles) writes:
>     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.

What does EXPLAIN show in the two cases?  What PG version is this?

			regards, tom lane

Wed, 07 Nov 2001 12:57:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: performance problem with 3-column indexes 