X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D00CB9DCB59 for ; Fri, 10 Mar 2006 14:31:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24231-04 for ; Fri, 10 Mar 2006 14:31:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id A5FF79DC999 for ; Fri, 10 Mar 2006 14:31:13 -0400 (AST) 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 k2AIVDY6007330; Fri, 10 Mar 2006 13:31:13 -0500 (EST) To: "Marc Morin" cc: pgsql-performance@postgresql.org Subject: Re: Trouble managing planner for timestamptz columns In-reply-to: <2BCEB9A37A4D354AA276774EE13FB8C2BCC46E@mailserver.sandvine.com> References: <2BCEB9A37A4D354AA276774EE13FB8C2BCC46E@mailserver.sandvine.com> Comments: In-reply-to "Marc Morin" message dated "Fri, 10 Mar 2006 12:30:41 -0500" Date: Fri, 10 Mar 2006 13:31:13 -0500 Message-ID: <7329.1142015473@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.112 required=5 tests=[AWL=0.112] X-Spam-Score: 0.112 X-Spam-Level: X-Archive-Number: 200603/139 X-Sequence-Number: 17576 "Marc Morin" writes: > We tend to analyze these tables every day or so and this doesn't always > prove to be sufficient.... Seems to me you just stated your problem. Instead of having the planner make wild extrapolations, why not set up a cron job to analyze these tables more often? Or use autovacuum which will do it for you. > Since the table is so large and the system is busy (disk not idle at > all), doing an analyze on this table in the production system can take > 1/2 hour! (statistics collector set to 100). I'd believe that for vacuum analyze, but analyze alone should be cheap. Have you perhaps got some weird datatypes in the table? Maybe you should back off the stats target a bit? We do support analyzing selected columns, so you might try something like a cron job analyzing only the timestamp column, with a suitably low stats target for that column. This would yield numbers far more reliable than any extrapolation the planner could do. regards, tom lane