From: Jona <jonanews@oismail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad choice of query plan from PG 7.3.6 to PG 7.3.9
Date: Sun, 08 May 2005 13:10:09 +0200
Message-ID: <427DF391.9060708@oismail.com> (raw)
In-Reply-To: <2901.1115480459@sss.pgh.pa.us>
References: <4279ECC1.4050000@oismail.com>
<24106.1115308954@sss.pgh.pa.us>
<427B1E0A.3000108@oismail.com>
<427B2D9E.2030804@familyhealth.com.au>
<427B51AB.4060501@oismail.com>
<13858.1115387983@sss.pgh.pa.us>
<427CADD3.6050106@oismail.com>
<2901.1115480459@sss.pgh.pa.us>
Sorry Tom, misread your mail! My bad :-(
I believe the following is the data you need ?
Live Server
relname relpages
ctp_statcon 72
statcon_pk 135
Test Server
relname relpages
ctp_statcon 34
statcon_pk 28
Have executed the following query to obtain that data:
SELECT relname, relpages
FROM pg_class
WHERE relname = 'statcon_pk' OR relname = 'sc2ctp_fk' OR relname =
'sc2mtp_fk' OR relname = 'sc2sc_fk' OR relname = 'ctp_statcon'
The size difference for the index is surprisingly big I think,
considering that there's only around 1000 rows more in the table on the
live server than on the server.
Count for Live Server: 12597
Count for Test Server: 11494
Any insight into this?
Cheers
Jona
PS: The meta data for the table is:
CREATE TABLE statcon_tbl
(
id serial NOT NULL,
data bytea,
wm bool DEFAULT 'FALSE',
created timestamp DEFAULT now(),
modified timestamp DEFAULT now(),
enabled bool DEFAULT 'TRUE',
bitsperpixel int4 DEFAULT 0,
mtpid int4,
sctid int4,
ctpid int4,
CONSTRAINT statcon_pk PRIMARY KEY (id),
CONSTRAINT sc2ctp_fk FOREIGN KEY (ctpid) REFERENCES contype_tbl (id)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT sc2mtp_fk FOREIGN KEY (mtpid) REFERENCES mimetype_tbl (id)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT sc2sct_fk FOREIGN KEY (sctid) REFERENCES statcontrans_tbl
(id) ON UPDATE CASCADE ON DELETE CASCADE
)
WITHOUT OIDS;
CREATE INDEX ctp_statcon ON statcon_tbl USING btree (sctid, ctpid);
Tom Lane wrote:
>Jona <jonanews@oismail.com> writes:
>
>
>>anyway, here's the info for relpages:
>>Live Server: 424
>>Test Server: 338
>>
>>
>
>I was asking about the indexes associated with the table, not the table
>itself.
>
> regards, tom lane
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jonanews@oismail.com, tgl@sss.pgh.pa.us, chriskl@familyhealth.com.au
Subject: Re: Bad choice of query plan from PG 7.3.6 to PG 7.3.9
In-Reply-To: <427DF391.9060708@oismail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox