Re: Slow count(*) again...

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