X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 021E553594 for ; Sun, 8 May 2005 08:06:49 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59216-10 for ; Sun, 8 May 2005 11:06:38 +0000 (GMT) Received: from oismail.com (unknown [38.117.22.100]) by svr1.postgresql.org (Postfix) with ESMTP id 4CC7B5295A for ; Sun, 8 May 2005 08:06:37 -0300 (ADT) Received: from [62.198.128.25] ([62.198.128.25]) (authenticated bits=0) by oismail.com (8.12.11/8.12.11) with ESMTP id j48B6CI5003493 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Sun, 8 May 2005 07:06:22 -0400 Message-ID: <427DF391.9060708@oismail.com> Date: Sun, 08 May 2005 13:10:09 +0200 From: Jona User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Christopher Kings-Lynne , pgsql-performance@postgresql.org Subject: Re: Bad choice of query plan from PG 7.3.6 to PG 7.3.9 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> In-Reply-To: <2901.1115480459@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------050802080700010108030902" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.586 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, HTML_50_60, HTML_MESSAGE, HTML_TAG_EXIST_TBODY, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200505/74 X-Sequence-Number: 12238 This is a multi-part message in MIME format. --------------050802080700010108030902 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 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 > > --------------050802080700010108030902 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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

--------------050802080700010108030902--