AW: [HACKERS] Really slow query on 6.4.2

> Unfortunately, if you haven't done a vacuum, the system effectively
> assumes that all your tables are tiny. I think this is a brain-dead
> default, but haven't had much luck convincing anyone else that the
> default should be changed.
>
I totally agree with Tom Lane here. Let me try to give some arguments.

1. If you have a user that does vacuum analyze regularly, we can
convince him to do vacuum analyze right after table creation, if he
knows the table will be tiny.

2. We have an application where the size of 20 tables changes from
0 to ~200000 rows in 3 hours. To have accurate statistics during the day we
would need to analyze at least every 20 min.
This was not acceptable during those 3 hours.
So we took the approach to tune the sql to work properly without ever
doing statistics.
This works perfectly on our Informix installation, since Informix has
a tuning parameter, that tells it, that an index has to be used iff
possible even if cost is higher, and the default for table size is 100.

3. There are two types of popular optimizers, rule and cost based.
A good approach is to behave rule based lacking statistics and cost
based with statistics. An easy way to achieve this is to choose
reasonable defaults for the statistics before accurate statistics
are made.

4. Those doing statistics will most likely not leave out a few tables, thus
creating an undefined state where the optimizer would behave rule
and cost based.

5. Actually postgresql has behaved in this manner because of certain
"bugs" in the optimizer. Recently a lot of those "bugs" have been
identified and "fixed", thus destroying the defacto rule based
behavior.

If the defaults are not changed, behavior of the overall system will
actually be changed for the case where statistics are lacking, when the
optimizer is improved to actually behave cost based under all
circumstances.

Andreas