Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 027A11337B71 for ; Tue, 12 Oct 2010 12:39:36 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 87692-02 for ; Tue, 12 Oct 2010 15:39:25 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from crestone.coronasolutions.com (crestone.coronasolutions.com [66.45.104.24]) by mail.postgresql.org (Postfix) with ESMTP id 650001337B58 for ; Tue, 12 Oct 2010 12:39:25 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by crestone.coronasolutions.com (Postfix) with ESMTP id 75D384E1AF for ; Tue, 12 Oct 2010 09:39:23 -0600 (MDT) X-Virus-Scanned: Debian amavisd-new at coronasolutions.com Received: from crestone.coronasolutions.com ([127.0.0.1]) by localhost (localhost.localdomain [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 6OpxPVu3EOu6 for ; Tue, 12 Oct 2010 09:39:20 -0600 (MDT) Received: from mac.local (unknown [10.1.250.14]) by crestone.coronasolutions.com (Postfix) with ESMTPA id ED3AA4E1AE for ; Tue, 12 Oct 2010 09:39:19 -0600 (MDT) Message-ID: <4CB48127.6090200@drivefaster.net> Date: Tue, 12 Oct 2010 09:39:19 -0600 From: Dan Harris User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US; rv:1.9.2.9) Gecko/20100915 Lightning/1.0b2 Thunderbird/3.1.4 MIME-Version: 1.0 To: pgsql-performance Subject: Re: Slow count(*) again... References: <201010091626.18636.neil.whelchel@gmail.com> <4CB11CC7.8070300@vmsinfo.com> <4CB167CA.9070109@postnewspapers.com.au> <4CB1F46E.80205@vmsinfo.com> <90E30938-1E5E-4DCA-A1FB-AEECEB76A70D@richrelevance.com> In-Reply-To: <90E30938-1E5E-4DCA-A1FB-AEECEB76A70D@richrelevance.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201010/156 X-Sequence-Number: 40575 On 10/11/10 8:02 PM, Scott Carey wrote: > would give you a 1MB read-ahead. Also, consider XFS and its built-in defragmentation. I have found that a longer lived postgres DB will get extreme > file fragmentation over time and sequential scans end up mostly random. On-line file defrag helps tremendously. > We just had a corrupt table caused by an XFS online defrag. I'm scared to use this again while the db is live. Has anyone else found this to be safe? But, I can vouch for the fragmentation issue, it happens very quickly in our system. -Dan