Skip site navigation (1) Skip section navigation (2)

Re: Inconsistent performance

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: Christopher Browne <cbbrowne(at)acm(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org, Joseph Bove <jbove(at)vetstar(dot)com>
Subject: Re: Inconsistent performance
Date: 2003-09-16 07:09:05
Message-ID: nbcdmv09dtu6go0k1nnncngmkfp40fc6bv@email.aon.at (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 15 Sep 2003 22:26:45 -0400, Christopher Browne
<cbbrowne(at)acm(dot)org> wrote:
>> select count (*) from table;
>The only possible plan for THAT query will involve a seq scan of the
>whole table.  If the postmaster already has the data in cache, it
>makes sense for it to run in 1 second.  If it has to read it from
>disk, 12 seconds makes a lot of sense.

Yes.  And note that the main difference is between having the data in
memory and having to fetch it from disk.  I don't believe that this
difference can be explained by 9000 read calls hitting the operating
system's cache.

>You might want to increase the "shared_buffers" parameter in
>postgresql.conf; that should lead to increased stability of times as
>it should be more likely that the data in "table" will remain in
>cache.

Let's not jump to this conclusion before we know what's going on.

Joseph Bove <jbove(at)vetstar(dot)com> wrote in another message above:
| I did have shared_buffers and sort_mem both set higher originally (15000, 
| 32168)

As I read this I think he meant "... and had the same performance
problem."

Joseph, what do you get, if you run that
	 EXPLAIN ANALYSE SELECT count(*) ...
several times?  What do vmstat and top show while the query is
running?  Are there other processes active during or between the runs?
What kind of processes?  Postgres backends?  Web server? ...

Servus
 Manfred

In response to

Responses

pgsql-performance by date

Next:From: Paul ThomasDate: 2003-09-16 07:44:48
Subject: Re: Attempt at work around of int4 query won't touch int8 index ...
Previous:From: Tom LaneDate: 2003-09-16 05:19:00
Subject: Re: restore time: sort_mem vs. checkpoing_segments

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group