From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Pam Ozer <pozer@automotive.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow Query- Bad Row Estimate
Date: Fri, 29 Oct 2010 16:39:56 -0500
Message-ID: <4CCAF8DC0200002500036FD5@gw.wicourts.gov> (raw)
In-Reply-To: <216FFB77CBFAEE4B8EE4DF0A939FF1D1018354@mail-001.corp.automotive.com>
References: <216FFB77CBFAEE4B8EE4DF0A939FF1D1018354@mail-001.corp.automotive.com>
"Ozer, Pam" <pozer@automotive.com> wrote:
> Is more information needed?
Table layouts of the tables involved (including indexes) would be
interesting. A description of the machine would be useful,
including OS, CPUs, RAM, and disk system.
I know you said you might have trouble changing the config, but some
of these seem problematic.
> shared_buffers = 500MB
> effective_cache_size = 1000MB
> max_connections = 100
> temp_buffers = 100MB
So you will allow up to 10GB to be tied up in space reserved for
temporary tables, but only expect to cache 1GB of your database?
That hardly seems optimal.
> work_mem = 100MB
That could be another 10GB or more in work memory at any moment, if
each connection was running a query which needed one work_mem
allocation.
> seq_page_cost = 1.0
> random_page_cost = 1.1
> cpu_tuple_cost = 0.1
> cpu_index_tuple_cost = 0.05
> cpu_operator_cost = 0.01
Those settings are OK if the active portion of the database is fully
cached. Is it?
> default_statistics_target = 1000
If plan times get long with complex queries, you might want to back
that off; otherwise, OK.
> autovacuum_max_workers = 1
That seems like a bad idea. Allowing multiple workers helps reduce
bloat and improve statistics. If autovacuum is affecting
performance, you would be better off tweaking the autovacuum cost
limits.
-Kevin
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: Kevin.Grittner@wicourts.gov, pozer@automotive.com
Subject: Re: Slow Query- Bad Row Estimate
In-Reply-To: <4CCAF8DC0200002500036FD5@gw.wicourts.gov>
* 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