Re: Running a query twice to ensure cached results.

Ron,

On 6/8/06 11:49 AM, "Ron Mayer" <rm_pg(at)cheapcomplexdevices(dot)com> wrote:

> Experimental results here suggest that for larger tables Linux seems
> to detect a seq-scan and not bother caching. It's very reproducible
> for me here to do a reboot and not see the full speedup on a seq_scan
> until the third time I run a query.su

What you are seeing is the now infamous "Postgres writes a table one more
time after loading" behavior.

Simon Riggs once dug into it to find the root cause, and I no longer recall
exactly why, but after you've loaded data, the first seq scan will re-write
some large portion of the data while doing the initial scan. This wreaks
havoc on normal benchmarking practices.

Tom - can you explain what's going on with this? It seems to write more
than just the contents of the WAL, so it's not a flush of the WAL writes
AFAICT.

- Luke