Neil Whelchel <neil.whelchel@gmail.com> writes:
> Insert the data into one table:
> crash:~# time psql -U test test -q < log.sql
> real 679m43.678s
> user 1m4.948s
> sys 13m1.893s
> crash:~# echo 3 > /proc/sys/vm/drop_caches
> crash:~# time psql -U test test -c "SELECT count(*) FROM log;"
> count
> ----------
> 10050886
> (1 row)
> real 0m11.812s
> user 0m0.000s
> sys 0m0.004s
> crash:~# time psql -U test test -c "SELECT count(*) FROM log;"
> count
> ----------
> 10050886
> (1 row)
> real 0m3.737s
> user 0m0.000s
> sys 0m0.000s
> As can be seen here, the cache helps..
That's probably got little to do with caching and everything to do with
setting hint bits on the first SELECT pass.
I concur with Mark's question about whether your UPDATE pushed the table
size across the limit of what would fit in RAM.
regards, tom lane