Re: Postgresqlism & Vacuum?

At 01:13 PM 14-04-2000 +0800, Thomas wrote:
>There has been effort to speed up the vacuuming process, but this isn't the
>cure.  I believe the fault lies on the optimizer.
>
>For eg, in Bruce Momjian's FAQ 4.9:
>
>   PostgreSQL does not automatically maintain statistics. One has to make
>   an explicit vacuum call to update the statistics. After statistics are
>   updated, the optimizer knows how many rows in the table, and can
>   better decide if it should use indices. Note that the optimizer does
>   not use indices in cases when the table is small because a sequential
>   scan would be faster.

Is it too difficult/expensive for Postgresql to keep track of how many
committed rows there are in each table? Then count(*) of the whole table
could be faster too.

Since it's just for optimization it could perhaps keep a rough track of how
many rows would be selected for the past X indexed searches of a table, so
as to better decide which index to use first. Right now it seems like the
optimizer can't learn a thing till the database takes a nap and dreams
about statistics. I prefer the database to be able to learn a few things
before having to take a nap. And then maybe it will only need to take a nap
once every few weeks/months.

Also it's better for the optimizer to be good at figuring which index to
use, than figure whether to use indexes at all. Because in most cases the
people creating indexes on tables _should_ know whether to use indexes at
all. So if there's an index use it. So what if it's a bit slower when
things are small. I put in indexes to make sure that things are still ok
when things get big!

How many people care about the "slow down" when things are small? It's
still fast! If things are going to stay small, then the database admin
should just drop the index.

Often predictable degradation is more useful than academically optimum.

Cheerio,

Link.