Neil Whelchel <neil(dot)whelchel(at)gmail(dot)com> wrote:
> crash:~# time psql -U test test -c "UPDATE log SET
> raw_data=raw_data+1"
> UPDATE 10050886
>
> real 14m13.802s
> user 0m0.000s
> sys 0m0.000s
>
> crash:~# time psql -U test test -c "SELECT count(*) FROM log;"
> count
> ----------
> 10050886
> (1 row)
>
> real 3m32.757s
> user 0m0.000s
> sys 0m0.000s
>
> Just to be sure:
> crash:~# time psql -U test test -c "SELECT count(*) FROM log;"
> count
> ----------
> 10050886
> (1 row)
>
> real 2m38.631s
> user 0m0.000s
> sys 0m0.000s
>
> It looks like cache knocked about a minute off
That's unlikely to be caching, since you just updated the rows.
It's much more likely to be one or both of rewriting the rows as you
read them to set hint bits or competing with autovacuum.
The large increase after the update probably means you went from a
table which was fully cached to something larger than the total
cache.
-Kevin