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 EE87E53869 for ; Fri, 22 Apr 2005 13:16:47 -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 81081-09 for ; Fri, 22 Apr 2005 16:16:44 +0000 (GMT) Received: from out1.smtp.messagingengine.com (out1.smtp.messagingengine.com [66.111.4.25]) by svr1.postgresql.org (Postfix) with ESMTP id 1D17F53873 for ; Fri, 22 Apr 2005 13:16:43 -0300 (ADT) Received: from web2.messagingengine.com (web2.internal [10.202.2.211]) by frontend1.messagingengine.com (Postfix) with ESMTP id EC54EC77B44; Fri, 22 Apr 2005 12:16:41 -0400 (EDT) Received: by web2.messagingengine.com (Postfix, from userid 99) id 1C34A27C4; Fri, 22 Apr 2005 12:16:36 -0400 (EDT) Message-Id: <1114186596.23462.232500712@webmail.messagingengine.com> X-Sasl-Enc: lCFOjmiOYPMGvTADdJng2HD01cyfdKT/owCSZx4NJf+e 1114186596 From: "David Roussel" To: "Tom Lane" Cc: "Bill Chandler" , "pgsql-perform" Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="ISO-8859-1" MIME-Version: 1.0 X-Mailer: MIME::Lite 1.5 (F2.73; T1.001; A1.64; B3.05; Q3.03) References: <20050421170018.2998.qmail@web51403.mail.yahoo.com> <27013.1114149431@sss.pgh.pa.us> <869771629a1d24a91b8df779f7cddfe9@diroussel.xsmail.com> <329.1114178793@sss.pgh.pa.us> Subject: Re: Index bloat problem? In-Reply-To: <329.1114178793@sss.pgh.pa.us> Date: Fri, 22 Apr 2005 17:16:36 +0100 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/615 X-Sequence-Number: 12012 On Fri, 22 Apr 2005 10:06:33 -0400, "Tom Lane" said: > David Roussel writes: > > |dave_data_update_events r 1593600.0 40209 > > |dave_data_update_events_event_id_key i 1912320.0 29271 > > Hmm ... what PG version is this, and what does VACUUM VERBOSE on > that table show? PG 7.4 The disparity seems to have sorted itself out now, so hampering futher investigations. I guess the regular inserts of new data, and the nightly deletion and index recreation did it. However, we did suffer reduced performance and the strange cardinality for several days before it went away. For what it's worth.. ndb=# vacuum verbose iso_pjm_data_update_events; INFO: vacuuming "public.iso_pjm_data_update_events" INFO: index "iso_pjm_data_update_events_event_id_key" now contains 1912320 row versions in 29271 pages DETAIL: 21969 index pages have been deleted, 20000 are currently reusable. CPU 6.17s/0.88u sec elapsed 32.55 sec. INFO: index "iso_pjm_data_update_events_lds_idx" now contains 1912320 row versions in 7366 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 3.52s/0.57u sec elapsed 14.35 sec. INFO: index "iso_pjm_data_update_events_obj_id_idx" now contains 1912320 row versions in 7366 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 3.57s/0.58u sec elapsed 12.87 sec. INFO: "iso_pjm_data_update_events": found 0 removable, 1912320 nonremovable row versions in 40209 pages DETAIL: 159384 dead row versions cannot be removed yet. There were 745191 unused item pointers. 0 pages are entirely empty. CPU 18.26s/3.62u sec elapsed 74.35 sec. VACUUM After each insert is does this... VACUUM ANALYZE iso_pjm_DATA_UPDATE_EVENTS VACUUM ANALYZE iso_pjm_CONTROL Each night it does this... BEGIN DROP INDEX iso_pjm_control_obj_id_idx DROP INDEX iso_pjm_control_real_name_idx DROP INDEX iso_pjm_data_update_events_lds_idx DROP INDEX iso_pjm_data_update_events_obj_id_idx CREATE UNIQUE INDEX iso_pjm_control_obj_id_idx ON iso_pjm_control(obj_id) CLUSTER iso_pjm_control_obj_id_idx ON iso_pjm_control CREATE UNIQUE INDEX iso_pjm_control_real_name_idx ON iso_pjm_control(real_name) CREATE INDEX iso_pjm_data_update_events_lds_idx ON iso_pjm_data_update_events(lds) CREATE INDEX iso_pjm_data_update_events_obj_id_idx ON iso_pjm_data_update_events(obj_id) COMMIT Note there is no reference to iso_pjm_data_update_events_event_id_key which is the index that went wacky on us. Does that seem weird to you? Thanks David