Re: Index Scans become Seq Scans after VACUUM ANALYSE

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...)

> (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 --- for one
thing, do you really want your plans changing on the basis of activity
of other backends?

One place where this does fall down is in nestloops with inner index
scans --- if we know that the inner query will be evaluated multiple
times, then we should give it some kind of discount for cache effects.
Investigating this is on the todo list...

> Also, can it distinguish between data in pg internal cache (shared
> memory) and data in OS filesystem cache ?

Currently we treat those alike. Yeah, the OS cache is slower to get to,
but in comparison to a physical disk read I think the difference is
insignificant.

regards, tom lane