Re: Slow count(*) again...

Neil Whelchel <neil(dot)whelchel(at)gmail(dot)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