Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B232B633DAA for ; Mon, 9 Nov 2009 08:22:13 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 37246-04 for ; Mon, 9 Nov 2009 12:22:05 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f213.google.com (mail-bw0-f213.google.com [209.85.218.213]) by mail.postgresql.org (Postfix) with ESMTP id C5546633286 for ; Mon, 9 Nov 2009 08:22:05 -0400 (AST) Received: by bwz5 with SMTP id 5so3502131bwz.3 for ; Mon, 09 Nov 2009 04:22:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=o7SovH+Cmsi66yQhkFswQLsQzBRtXvgbB4veJZB54f0=; b=HEwkAgNZlaXivIGj7nE/F6oYHgSv0ZDojW6lUExU22IUa2RsGUrKDsibty9vswZuwG QnOxODdZh4R7HvhN1gmzMCkkWXpvnONnVIsQtoPSIhcwHSWG34GyY7G++9pzIUeyKpkm d7xMzFRnJFLmgM65m7rDe+IzkcmDjdEuiPckA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=UKZsLyRJDvFCi1fcHGOvTsoFVO5RTueu5LbvNsXbKkgVPnjdqgOChQWtg+8Evn6GYR al9xJa2WmdLUdkuOLdhZxcCs2Ic4XwKwPvyyj8OgOZWit3u0FnsnKpb8W6i5vz0D21CP IMpBrPvJPCoJYuWAuheoIMlGviUOaLg9o1GZs= MIME-Version: 1.0 Received: by 10.239.190.227 with SMTP id y35mr755445hbh.212.1257769323072; Mon, 09 Nov 2009 04:22:03 -0800 (PST) In-Reply-To: <009601ca6030$0e731440$2b593cc0$@com> References: <008801ca6021$c1cc7630$45656290$@com> <18869.1257650960@sss.pgh.pa.us> <009601ca6030$0e731440$2b593cc0$@com> Date: Mon, 9 Nov 2009 07:22:02 -0500 Message-ID: <603c8f070911090422t52412697hf19b222a033f729@mail.gmail.com> Subject: Re: CREATE TABLE slowing down significantly over time From: Robert Haas To: Aris Samad-Yahaya Cc: Tom Lane , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200911/87 X-Sequence-Number: 36255 On Sat, Nov 7, 2009 at 11:58 PM, Aris Samad-Yahaya wrote: > We vacuum analyze nightly, and vacuum normally ad-hoc (but we're going to > schedule this weekly moving forward). > > Interesting pointer about system catalog bloat. I tried to vacuum full the > system catalog tables (pg_*), and the performance for creating a single > table manually improved dramatically (back to what it used to be), but as > soon as I created the next schema, the performance went back down to the > same level. > > So there's a clue there somewhere. Next I will try to vacuum full the entire > database. You should really enable autovacuum. You'll probably have to VACUUM FULL and REINDEX to clean everything up, but after that autovacuum should be MUCH more effective than a nightly vacuum run. If you're running some ancient Pg version where autovacuum is not enabled by default, you should also consider upgrading. There are a lot of goodies (including performance enhancements) in newer versions. ...Robert