On 14/05/13 10:10, Marti Raudsepp wrote:
On Mon, May 13, 2013 at 6:01 PM, ach <alanchines@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
