From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marc Morin <marc@sandvine.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Trouble managing planner for timestamptz columns
Date: Fri, 10 Mar 2006 13:31:13 -0500
Message-ID: <7329.1142015473@sss.pgh.pa.us> (raw)
In-Reply-To: <2BCEB9A37A4D354AA276774EE13FB8C2BCC46E@mailserver.sandvine.com>
References: <2BCEB9A37A4D354AA276774EE13FB8C2BCC46E@mailserver.sandvine.com>
"Marc Morin" <marc@sandvine.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, marc@sandvine.com
Subject: Re: Trouble managing planner for timestamptz columns
In-Reply-To: <7329.1142015473@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox