Re: Index Scans become Seq Scans after VACUUM ANALYSE

On Wed, 2002-04-17 at 22:43, Tom Lane wrote:
> Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > OTOH, it is also important where the file is on disk. As seen from disk
> > speed test graphs on http://www.tomshardware.com , the speed difference
> > of sequential reads is 1.5 to 2.5 between inner and outer tracks.
>
> True. But if we use the same test file for both the sequential and
> random-access timings, hopefully the absolute speed of access will
> cancel out. (Again, it's the sort of thing that could use some
> real-world testing...)

What I was trying to say was thet if you test on one end you will get
wrong data for the other end of the same disk.

> > (The PG planner does try to account
> > for caching effects, but that's a separate estimate;
>
> > Will it make the random and seq read cost equal when cache size >
> > database size and enough queries are performed to assume that all data
> > is in cache.
>
> There isn't any attempt to account for the effects of data having been
> read into cache by previous queries. I doubt that it would improve the
> model to try to keep track of what the recent queries were

Perhaps some simple thing, like
number of pages read * cache size / database size

Or perhaps use some additional bookkeeping in cache logic, perhaps even
on per-table basis. If this can be made to use the same locks ás cache
loading/invalidation it may be quite cheap.

It may even exist in some weird way already inside the LRU mechanism.

>--- for one
> thing, do you really want your plans changing on the basis of activity
> of other backends?

If I want the best plans then yes. The other backends do affect
performance so the best plan would be to account for their activities.

If other backend is swapping like crazy the best plan may even be to
wait for it to finish before proceeding :)

----------------
Hannu