Re: Simple postgresql.conf wizard

Decibel! <decibel(at)decibel(dot)org> writes:

> Is there even a good way to find out what planning time was? Is there a way to
> gather that stat for every query a session runs?

\timing
explain select ...

> The thought occurs to me that we're looking at this from the wrong side of the
> coin. I've never, ever seen query plan time pose a problem with Postgres, even
> without using prepared statements.

I certainly have seen plan times be a problem. I wonder if you have too and
just didn't realize it. With a default_stats_target of 1000 you'll have
hundreds of kilobytes of data to slog through to plan a moderately complex
query with a few text columns. Forget about prepared queries, I've seen plan
times be unusable for ad-hoc interactive queries before.

> We've been talking about changing default_stats_target for at least 2 or 3
> years now. We know that the current value is causing problems. Can we at least
> start increasing it? 30 is pretty much guaranteed to be better than 10, even
> if it's nowhere close to an ideal value. If we start slowly increasing it then
> at least we can start seeing where people start having issues with query plan
> time.

How would you see anything from doing that? We only hear from people who have
problems so we only see half the picture. You would have no way of knowing
whether your change has helped or hurt anyone.

In any case I don't see "we know that the current value is causing problems"
as a reasonable statement. It's the *default* stats target. There's a reason
there's a facility to raise the stats target for individual columns.

As Dann said, "the idea that there IS a magic number is the problem". *Any*
value of default_stats_target will "cause" problems. Some columns will always
have skewed data sets which require unusually large samples, but most won't
and the system will run faster with a normal sample size for that majority.

The question is what value represents a good trade-off between the costs of
having large stats targets -- longer analyze, more data stored in
pg_statistics, more vacuuming of pg_statistics needed, longer plan times --
and the benefits of having larger stats targets -- fewer columns which need
raised stats targets.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!