Re: statistics target for columns in unique constraint?

От: Mark Kirkwood
Тема: Re: statistics target for columns in unique constraint?
Дата: ,
(см: обсуждение, исходный текст)
Ответ на: Re: statistics target for columns in unique constraint?  (Marti Raudsepp)
Ответы: Re: statistics target for columns in unique constraint?  (ach)
Список: pgsql-performance

On 14/05/13 10:10, Marti Raudsepp wrote:
> On Mon, May 13, 2013 at 6:01 PM, ach <> 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!



В списке pgsql-performance по дате сообщения:

От: Scott Marlowe
Сообщение: Re: Hardware suggestions for maximum read performance
От: Sameer Thakur
Сообщение: Predicate information in EXPLAIN Command