Neil,

> In the simple test I performed, raising the default_statistics_target 
> from 10 to 25 resulted in a 40% increase in the time to ANALYZE a 
> large table. (I picked 25 more or less at random -- would 15 or 20 be 
> better?)

I find that very interesting, since I haven't found much higher increases to 
be a proportionate penality.   For example, on an 11-column table raising 3 
columns to statistics=250 merely doubled the ANALYZE time.  I have not done 
exact timing, but would be happy to ....

>It may also be the case that for those people for whom 10 
> is an insufficient stats target, 25 is also insufficient.

It is.   I've found that "problem" queries, especially those caused by real, 
uneven distribution of data, require raising statistics to 150-400 in order 
to fix.  This is much to high a level to assign as a default.

> Any comments on whether increasing the default stats target is a good 
> idea for 7.5? (Details on the test I performed are included below)

No.   I don't think it's a good idea to raise the default for *all* columns; 
for one thing, I'd really hate to think what, say, a default stats of 100 
would do to a TEXT column with an average of 8K of data per row.

Further, in 7.5 we'll be introducing correlated stats for multi-column indexes 
(unless something's gone off with that?) which should help a lot of problem 
queries.  And change our whole emphasis on brute forcing analyze through 
increasing stats into the 100's.   

If you really want to tackle this issue, though, here's what I suggest:

1) add a GUC called default_statistics_indexed, which starts at say 100 or 50.
2) When ever the user indexes a column, automatically increase the stats
to the level in default_statistics_indexed, if they are at the level in 
default_statistics_target.

This will then give indexed columns "automatically" a somewhat higher level of 
stats analysis than other columns.   This should help a lot of "slow query" 
problems, yet effectively leave the selection of "important" columns in the 
hands of the DBA.   Make sense?

Also, another great feature in this department would be to extend the 
multi-column correlation statistics to cover foriegn keys, as a way of 
improving cross-table estimates.

Anyway, keep me in the loop on this, I have a lot of very complex databases I 
can test such issues on.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Mon, 8 Mar 2004 09:17:52 -0800
Josh Berkus <josh@agliodbs.com>
Re: raising the default default_statistics_target
Josh Berkus <josh@agliodbs.com> writes:
> It is.   I've found that "problem" queries, especially those caused by real, 
> uneven distribution of data, require raising statistics to 150-400 in order 
> to fix.  This is much to high a level to assign as a default.

That's basically what's bothering me about the suggestion to increase to
25 --- I'm dubious that it will do any good.

> Further, in 7.5 we'll be introducing correlated stats for multi-column indexes 
> (unless something's gone off with that?)

News to me.  It's certainly not there now.

> This will then give indexed columns "automatically" a somewhat higher
> level of stats analysis than other columns.

That is potentially a good idea.  There's still the question of what is
a reasonable default, though.

			regards, tom lane

Mon, 08 Mar 2004 13:08:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: raising the default default_statistics_target 
Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> > It is.   I've found that "problem" queries, especially those caused by real, 
> > uneven distribution of data, require raising statistics to 150-400 in order 
> > to fix.  This is much to high a level to assign as a default.
> 
> That's basically what's bothering me about the suggestion to increase to
> 25 --- I'm dubious that it will do any good.
> 
> > Further, in 7.5 we'll be introducing correlated stats for multi-column indexes 
> > (unless something's gone off with that?)
> 
> News to me.  It's certainly not there now.
> 
> > This will then give indexed columns "automatically" a somewhat higher
> > level of stats analysis than other columns.
> 
> That is potentially a good idea.  There's still the question of what is
> a reasonable default, though.

Do all the columns have to have the same number of statistics buckets? 
Could that stats collector adjust the number of buckets based on the
data somehow?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 8 Mar 2004 13:28:46 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: raising the default default_statistics_target
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Do all the columns have to have the same number of statistics buckets? 

They do not, but the effort spent by ANALYZE is proportional to the
largest stats target among all the columns of the table.

			regards, tom lane

Mon, 08 Mar 2004 13:37:53 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: raising the default default_statistics_target 
Tom,

> > Further, in 7.5 we'll be introducing correlated stats for multi-column 
indexes 
> > (unless something's gone off with that?)

This was discussed on Hackers in October, a complete implementation was shown, 
I thought it was committed at that time.   If not, what happened to it?

Dammit, it's impossible to find anything in the archives if you don't have 
some good keywords or at least the author.  Is the autor reading this?   Will 
you speak up?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Mon, 8 Mar 2004 11:07:12 -0800
Josh Berkus <josh@agliodbs.com>
Re: raising the default default_statistics_target
Josh Berkus <josh@agliodbs.com> writes:
> Further, in 7.5 we'll be introducing correlated stats for multi-column 
> indexes (unless something's gone off with that?)

> This was discussed on Hackers in October, a complete implementation was shown, 
> I thought it was committed at that time.   If not, what happened to it?

Are you sure you're not thinking of stats for functional indexes?

			regards, tom lane

Mon, 08 Mar 2004 14:19:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: raising the default default_statistics_target 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Do all the columns have to have the same number of statistics buckets? 
> 
> They do not, but the effort spent by ANALYZE is proportional to the
> largest stats target among all the columns of the table.

Could we use previous stats to determine how many buckets to use when
running ANALYZE.  Also, if columns have a different number of buckets,
does that mean that we don't have the same per-query overhead for a
larger stats target?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 8 Mar 2004 14:22:36 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: raising the default default_statistics_target
Tom,

> Are you sure you're not thinking of stats for functional indexes?

Positive.    I even remember seeing that the patch was accepted.

The patch specifically had to do with a multi-column correlation algorithm for 
improving the selectivity of multi-column indexes.

Problem is, with 1400 posts per month August to October, I can't find it, and 
the keywords that I think are obvious don't turn anything up.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Mon, 8 Mar 2004 11:41:06 -0800
Josh Berkus <josh@agliodbs.com>
Re: raising the default default_statistics_target
I think the thread you're thinking of is on or about this post:
http://archives.postgresql.org/pgsql-patches/2003-12/msg00039.php

Manfred Koizar produced a patch that modified index correlation by
sorting equal key values based on item pointers. The patch went as far
as getting accepted into the patch queue, but Tom raised some doubts
about it and it was subsequently removed. 

Robert Treat

On Mon, 2004-03-08 at 14:41, Josh Berkus wrote:
> Tom,
> 
> > Are you sure you're not thinking of stats for functional indexes?
> 
> Positive.    I even remember seeing that the patch was accepted.
> 
> The patch specifically had to do with a multi-column correlation algorithm for 
> improving the selectivity of multi-column indexes.
> 
> Problem is, with 1400 posts per month August to October, I can't find it, and 
> the keywords that I think are obvious don't turn anything up.
> 
> -- 
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


09 Mar 2004 09:33:48 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: raising the default default_statistics_target
Robert Treat <xzilla@users.sourceforge.net> writes:
> I think the thread you're thinking of is on or about this post:
> http://archives.postgresql.org/pgsql-patches/2003-12/msg00039.php
> Manfred Koizar produced a patch that modified index correlation by
> sorting equal key values based on item pointers. The patch went as far
> as getting accepted into the patch queue, but Tom raised some doubts
> about it and it was subsequently removed. 

Hm, that had nothing to do with multi-column correlation though.
I'm at a loss to think of any work that matches with Josh's
recollection.

			regards, tom lane

Tue, 09 Mar 2004 10:11:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: raising the default default_statistics_target 
Guys,

> Hm, that had nothing to do with multi-column correlation though.
> I'm at a loss to think of any work that matches with Josh's
> recollection.

Hmmmm .... it's possible that early e-mails about Manfred's patch claimed to 
improve performance for multi-column indexes.    

But it's also possible I'm remembering something else.

Darn it, though!  'cause multi-column correlation is one of our big issues on 
estimates for complex queries.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Tue, 9 Mar 2004 08:41:42 -0800
Josh Berkus <josh@agliodbs.com>
Re: raising the default default_statistics_target