From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Marti Raudsepp <marti@juffo.org>
To: ach <alanchines@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: statistics target for columns in unique constraint?
Date: Tue, 14 May 2013 10:42:33 +1200
Message-ID: <51916C59.6010700@catalyst.net.nz> (raw)
In-Reply-To: <CABRT9RBwmDxjq-tzLMh6rFX-suJYVQMiGSQAADsueg07rvB1dg@mail.gmail.com>
References: <1368457293547-5755256.post@n5.nabble.com>
<CABRT9RBwmDxjq-tzLMh6rFX-suJYVQMiGSQAADsueg07rvB1dg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 14/05/13 10:10, Marti Raudsepp wrote:
> On Mon, May 13, 2013 at 6:01 PM, ach <alanchines@gmail.com> wrote:
>> what I'm wondering is, since
>> the unique constraint already covers the whole table and all rows in
>> entirety, is it really necessary for statistics to be set that high on
>> those?
>
> AFAIK if there are exact-matching unique constraints/indexes for a
> query's WHERE clause, the planner will deduce that the query only
> returns 1 row and won't consult statistics at all.
>
>> Or does that only serve to slow down inserts to that table?
>
> It doesn't slow down inserts directly. Tables are analyzed in the
> background by autovacuum. However, I/O traffic from autovacuum analyze
> may slow down inserts running concurrently.
>
>
A higher number in stats target means larger stats structures - which in
turn means that the planning stage of *all* queries may be impacted -
e.g takes up more memory, slightly slower as these larger structures are
read, iterated over, free'd etc.
So if your only access is via a defined unique key, then (as Marti
suggests) - a large setting for stats target would seem to be unnecessary.
If you have access to a test environment I'd recommend you model the
effect of reducing stats target down (back to the default of 100 or even
to the old version default of 10).
A little - paranoia - maybe switch on statement logging and ensure that
there are no *other* ways this table is accessed...the fact that the
number was cranked up from the default is a little suspicious!
Regards
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: mark.kirkwood@catalyst.net.nz, marti@juffo.org, alanchines@gmail.com
Subject: Re: statistics target for columns in unique constraint?
In-Reply-To: <51916C59.6010700@catalyst.net.nz>
* 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