Re: Default Stats Revisited

On Wed, 10 Mar 2004, Josh Berkus wrote:

> Folks,
>
> Early on in the default_stats thread, I made a proposal that got dropped
> without discussion. I'd like to revisit it, because I still think it's a
> good idea.
>
> The Issue: The low default_stats_target of 10 is not sufficient for many
> complex queries involving multi-column correlation or oddly distributed data.
> Yet modestly increasing the stats target for *all* columns, as demonstrated,
> substantially increases the time required for Analyze, without gain on most
> queries.
>
> If only there were a way to automatically increas the default stats on only
> "important" columns, and not on other columns! Yet if we burden the DBA with
> flagging important colummns all over the database, we haven't saved him/her
> any work.
>
> Ah, but there is a way! Most "important" columns are already indicated ...
> because they are indexed. If we implemented a system where indexed columns
> would have a significantly higher stats_target than non-indexed columns, this
> might improve our default behavior without overburdening Analyze.
>
> Proposal: That we consider:
> -- adding a new GUC default_stats_indexed
> -- that this GUC be set initially to 100 if stats_target is 10
> -- that the system be adjusted to that indexed columns take their
> stats_target from default_stats_indexed and not default_stats_target
> -- that expressional indexes be ignored for this purpose, as implementation
> would be too complex, and they have their own stats anyway
>
> If this proposal is worth considering, I will spend some time building up a
> test case to demonstrate the cost and utility of the plan. With Neil's help,
> of course!

I like it. Would a multiplier be acceptable?
default_stats_index_multiplier = 10

Of course it would automatically be capped at 1000, etc...

I like the idea that if I'm increasing the default statistics target the
stats for indexed columns goes up too.