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 23E3E8BA485 for ; Fri, 18 Feb 2005 14:59:55 +0000 (GMT) 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 33448-02 for ; Fri, 18 Feb 2005 14:59:48 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 052988BA483 for ; Fri, 18 Feb 2005 14:59:51 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j1IExqv2018066; Fri, 18 Feb 2005 09:59:52 -0500 (EST) To: "Rodrigo Moreno" Cc: pgsql-performance@postgresql.org Subject: Re: RES: Degradation of postgres 7.4.5 on FreeBSD/CygWin In-reply-to: References: Comments: In-reply-to "Rodrigo Moreno" message dated "Fri, 18 Feb 2005 12:43:58 -0300" Date: Fri, 18 Feb 2005 09:59:52 -0500 Message-ID: <18065.1108738792@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/263 X-Sequence-Number: 10592 "Rodrigo Moreno" writes: > max_fsm_pages = 40000 > max_fsm_relations = 2000 > But why after 2 months the database has 1.3gb and after reimport on 900mb ? 40k pages = 320M bytes = 1/3rd of your database. Perhaps you need a larger setting for max_fsm_pages. However, 30% bloat of the database doesn't particularly bother me, especially when you are using infrequent vacuums. Bear in mind that, for example, the steady-state fill factor of a b-tree index is usually estimated at less than 70%. A certain amount of wasted space is not only intended, but essential for reasonable performance. What you need is to take a more detailed look at the behavior of that function that's getting so slow. Are the query plans changing? Is the loop iterating over many more rows than before? You haven't told us anything that would account for 100x slowdown. regards, tom lane