Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 20D1E9FB8A8 for ; Tue, 5 Jun 2007 11:35:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 03711-03-2 for ; Tue, 5 Jun 2007 11:34:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from hunley.homeip.net (hunley.homeip.net [69.12.222.146]) by postgresql.org (Postfix) with ESMTP id 24D079FBE6E for ; Tue, 5 Jun 2007 11:34:10 -0300 (ADT) Received: from douglas.hunley.homeip.net (d60-34-189.col.wideopenwest.com [65.60.189.34]) (authenticated bits=0) by hunley.homeip.net (8.13.1/8.13.1) with ESMTP id l55EY89K002071 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Tue, 5 Jun 2007 10:34:10 -0400 From: Douglas J Hunley To: Gregory Stark Subject: Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Date: Tue, 5 Jun 2007 10:34:04 -0400 User-Agent: KMail/1.9.7 Cc: pgsql-performance@postgresql.org References: <200706020913.32241.doug@hunley.homeip.net> <200706041601.08918.doug@hunley.homeip.net> <87ps4bl91g.fsf@oxford.xeocode.com> In-Reply-To: <87ps4bl91g.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200706051034.04087.doug@hunley.homeip.net> X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (hunley.homeip.net [69.12.222.146]); Tue, 05 Jun 2007 10:34:10 -0400 (EDT) X-Virus-Scanned: ClamAV 0.88.7/3360/Tue Jun 5 00:32:46 2007 on hunley.homeip.net X-Virus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.22 tagged_above=0 required=5 tests=AWL=0.270, BAYES_40=-0.185, FORGED_RCVD_HELO=0.135 X-Spam-Level: X-Archive-Number: 200706/69 X-Sequence-Number: 25102 On Monday 04 June 2007 17:11:23 Gregory Stark wrote: > Those plans look like they have a lot of casts to text in them. How have > you defined your indexes? Are your id columns really text? project table: Indexes: "project_pk" PRIMARY KEY, btree (id) "project_path" UNIQUE, btree (path) role table: Indexes: "role_pk" PRIMARY KEY, btree (id) role_default_user table: Indexes: "role_def_user_pk" PRIMARY KEY, btree (id) "role_def_u_prj_idx" UNIQUE, btree (role_id, default_user_class_id, project_id) role_operation table: Indexes: "role_operation_pk" PRIMARY KEY, btree (id) "role_oper_obj_oper" btree (object_type_id, operation_category, operation_name) "role_oper_role_id" btree (role_id) sfuser table: Indexes: "sfuser_pk" PRIMARY KEY, btree (id) "sfuser_username" UNIQUE, btree (username) projectmembership table: Indexes: "pjmb_pk" PRIMARY KEY, btree (id) "pjmb_projmember" UNIQUE, btree (project_id, member_id) "pjmb_member" btree (member_id) relationship table: Indexes: "relationship_pk" PRIMARY KEY, btree (id) "relation_origin" btree (origin_id) "relation_target" btree (target_id) "relation_type" btree (relationship_type_name) field_value table: Indexes: "field_value_pk" PRIMARY KEY, btree (id) "f_val_fid_val_idx" UNIQUE, btree (field_id, value) "field_class_idx" btree (value_class) "field_value_idx" btree (value) item table: Indexes: "item_pk" PRIMARY KEY, btree (id) "item_created_by_id" btree (created_by_id) "item_folder" btree (folder_id) "item_name" btree (name) and yes, the 'id' column is always: character varying type > And you don't have a 7.4 install around to compare the plans do you? I have a 7.3.19 db, if that would be useful -- Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 http://doug.hunley.homeip.net Whose cruel idea was it for the word "lisp" to have an "s" in it?