Re: Performance problem with a table with 38928077 record

What is a bit strange about this is that you can do this:

On Fri, Oct 7, 2011 at 6:04 AM, Giovanni Mancuso <gmancuso(at)babel(dot)it> wrote:

> select count(*) from dm_object_perm;
> count
> ----------
> 38928077
> (1 row)
>

But not this:

If i run "explain analyze select * from dm_object_perm;" it goes on for many
> hours.
>

If I had to guess, I'd bet that the second one is trying to spool the
resultset in memory someplace and that's driving the machine into swap. But
that's just a shot in the dark. You might want to use tools like top,
vmstat, iostat, free, etc. to see what the system is actually doing while
this is running. I'd start the query up, let it run for 10 minutes or so,
and then see whether the machine is CPU-bound or I/O-bound, and whether the
amount of swap in use is growing.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company