I have finished a first pass at the planner statistics and cost
estimation changes that I want to do for 7.2.  It's now time to see
how well the new code does in the real world.  Accordingly, if you've
had problems in the past with silly choices of plans, I'd like to ask
you to load your data into a test installation of current sources and
see if the planner is any brighter than before.

I realize that loading a bunch of data into a temporary installation is
a pain in the neck, but it'd be really great to get some feedback about
performance of the new code now, while we're still early enough in the
7.2 development cycle to do something about any problems that turn up.

If you're willing to help out, you can get current sources from the
CVS server, or from the nightly snapshot tarball (see the dev/
directory on your favorite Postgres FTP mirror).

Some highlights of the new code include:

* ANALYZE is now available as a separate command; you can run it without
also doing a VACUUM.  (Of course, VACUUM ANALYZE still works.)

* On large tables, ANALYZE uses a random sample of rows rather than
examining every row, so that it should take a reasonably short time
even on very large tables.  Possible downside: inaccurate stats.
We need to find out if the sample size is large enough.

* Statistics now include the "top ten" most common values, not just
the single most common value, plus an estimate of the total number of
distinct values in a column.  This should mean that selectivity
estimates for "column = something" estimates are a lot better than
before, especially for highly skewed data distributions.

* Statistics also include (for scalar datatypes) a histogram that
gives the boundary values dividing the data into ten
roughly-equal-population bins.  This should allow much better estimation
for inequality and range queries, again especially for skewed data
distributions.  (Note that "range queries" include such things as
anchored LIKE and regexp searches, plus now inet subnet searches thanks
to Alex Pilosov.)

* The magic number "ten" mentioned above is controllable via
ALTER TABLE tab ALTER COLUMN col SET STATISTICS statstarget.
Adjusting it gives a tradeoff between estimation accuracy and
time/space taken by ANALYZE.  We need to find out if ten is a good
default or not ... it might be too high or too low.

* There's also a physical-order-correlation statistic that should help
the planner deal with clustered indices better.  Whether it's good
enough, and whether the costs are correctly interpolated using it,
remain to be seen.

For more info see my original proposal at
http://fts.postgresql.org/db/mw/msg.html?mid=112714
and followup discussion.

			regards, tom lane

Sun, 17 Jun 2001 16:47:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Call for alpha testing: planner statistics revisions
On Sun, 17 Jun 2001, Tom Lane wrote:
> * On large tables, ANALYZE uses a random sample of rows rather than
> examining every row, so that it should take a reasonably short time
> even on very large tables.  Possible downside: inaccurate stats.
> We need to find out if the sample size is large enough.
How about letting the user specify how much of the table should be
examined? So if you know the data is homogenous you can just specify a
smaller sample than normal and if you have time you can have the whole
table scanned.

- Einar Karttunen


Thu, 21 Jun 2001 14:38:56 +0300 (EEST)
Einar Karttunen <ekarttun@cs.Helsinki.FI>
Re: Call for alpha testing: planner statistics revisions
Tom Lane wrote:
> 
> I have finished a first pass at the planner statistics and cost
> estimation changes that I want to do for 7.2.  It's now time to see
> how well the new code does in the real world...
> 
> Some highlights of the new code include:
> 
> * ANALYZE is now available as a separate command; you can run it without
> also doing a VACUUM.  (Of course, VACUUM ANALYZE still works.)

What is the impact of this newly isolated ANALYZE command on the need
and/or frequency for VACUUMs?  

I'd like to reduce the frequency of VACUUMs given my understanding has
been that updates/inserts/deletes are a no-no during VACUUMs (that was
6.5.x era hearsay), and we lock people out during VACUUMs (VACUUM
ANALYZE, that is).  

Regards,
Ed Loehr

Thu, 21 Jun 2001 14:05:12 -0500
Ed Loehr <efl@pobox.com>
Re: [GENERAL] Call for alpha testing: planner statistics revisions
Ed Loehr <efl@pobox.com> writes:
> Tom Lane wrote:
>> * ANALYZE is now available as a separate command; you can run it without
>> also doing a VACUUM.  (Of course, VACUUM ANALYZE still works.)

> What is the impact of this newly isolated ANALYZE command on the need
> and/or frequency for VACUUMs?  

None really.  By the time 7.2 is out, I expect we will also have a
more lightweight form of VACUUM, and so running VACUUM ANALYZE as a
reasonably frequent background operation will still be the norm.

			regards, tom lane

Thu, 21 Jun 2001 16:02:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Call for alpha testing: planner statistics revisions 