Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 5E8E49FA316 for ; Sat, 23 Dec 2006 15:52:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 05841-07 for ; Sat, 23 Dec 2006 15:52:14 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id D5E279FA111 for ; Sat, 23 Dec 2006 15:52:13 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from spunkymail-a12.dreamhost.com (sd-green-bigip-177.dreamhost.com [208.97.132.177]) by svr4.postgresql.org (Postfix) with ESMTP id 39E745AF894 for ; Sat, 23 Dec 2006 19:52:13 +0000 (GMT) Received: from [192.168.1.238] (adsl-75-31-110-76.dsl.irvnca.sbcglobal.net [75.31.110.76]) by spunkymail-a12.dreamhost.com (Postfix) with ESMTP id CF8397FAC; Sat, 23 Dec 2006 11:52:11 -0800 (PST) Message-ID: <458D88E6.80609@araisoft.com> Date: Sat, 23 Dec 2006 11:52:06 -0800 From: Benjamin Arai User-Agent: Thunderbird 1.5.0.8 (Windows/20061025) MIME-Version: 1.0 To: Jeff Frost Cc: pgsql-admin@postgresql.org Subject: Re: Problem with large table not using indexes (I think) References: <458D82D0.5050903@araisoft.com> In-Reply-To: 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=0.19 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200612/305 X-Sequence-Number: 24097 One more note about my problem, when you run a query on older data in the table then it work great but if you query newer data then is very slow. Ex. SELECT * from my_table WHERE date >=12/1/2005 and date <= 12/1/2006; <- slow SELECT * from my_table WHERE date >=12/1/2002 and date <= 12/1/2003; <- fast It just has to do with the new data for some reason. Benjamin Jeff Frost wrote: > On Sat, 23 Dec 2006, Benjamin Arai wrote: > >> The largest table in my database (30GB) has mysteriously went from >> taking milli-seconds to perform a query to minutes. This disks are >> fine and I have a 4GB shared_memory. Could this slow down have to do >> with the fsm_max_pages or something else like that? I made it larger >> but the queries still taking a long time. I do daily vacuum's but I >> don't run it with -z or --full. I would like to avoid doing a --full >> if possible because it would literally take over a week to complete. >> Any help would be greatly appreciated. > > Benjamin, > > When is the last time you ran ANALYZE? That's what the -z option > does. If you're only vacuuming once daily, you should definitely > analyze with the -z flag as well. >