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.