Re: Slow count(*) again...

Luca Tettamanti <kronos.it@gmail.com>

From: Luca Tettamanti <kronos.it@gmail.com>

To: Jon Nelson <jnelson+pgsql@jamponi.net>

Cc: mladen.gogala@vmsinfo.com, "david@lang.hm" <david@lang.hm>, Craig Ringer <craig@postnewspapers.com.au>, Vitalii Tymchyshyn <tivv00@gmail.com>, "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>

Date: 2010-10-12T13:19:52Z

Lists: pgsql-hackers, pgsql-performance

On Tue, Oct 12, 2010 at 3:07 PM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> On Tue, Oct 12, 2010 at 7:27 AM, Mladen Gogala
> <mladen.gogala@vmsinfo.com> wrote:
>>
>> So, the results weren't cached the first time around. The explanation is the
>> fact that Oracle, as of the version 10.2.0, reads the table in the private
>> process memory, not in the shared buffers.  This table alone is  35GB in
>> size,  Oracle took 2 minutes 47 seconds to read it using the full table
>> scan. If I do the same thing with PostgreSQL and a comparable table,
>> Postgres is, in fact, faster:
>
> Well, I didn't quite mean that - having no familiarity with Oracle I
> don't know what the alter system statement does, but I was talking
> specifically about the linux buffer and page cache. The easiest way to
> drop the linux caches in one fell swoop is:
>
> echo 3 > /proc/sys/vm/drop_caches

AFAIK this won't affect Oracle when using direct IO (which bypasses
the page cache).

Luca