Large difference between elapsed time and run time for queries

From: "Scott Buchan" <sbuchan(at)technicacorp(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Large difference between elapsed time and run time for queries
Date: 2003-03-10 18:58:26
Message-ID: 71F044551C53974EB0735B3737EFE2FC088628@wwwserver
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,

While running benchmarks for my database, I am seeing a large difference in
the elapsed time (from stats collected in the logs) and run time (running
explain analyze on the query using ./psql <database>) for each of my
queries. The database is being ran on a sunfire 880 with 4 750mhz
processors with 8 G RAM running solaris 8

I am simulating 200 user connections each running 6 select queries on 1
indexed table with 50,000 records. The elapsed time for the queries average
around 2.5 seconds while if I run the query using explain analyze while the
test is running, the run time is around 300 ms although it takes much longer
(few seconds) to display the results. If I reduce the number of concurrent
connections to 100 then the run time and elapsed time for the queries are
the same.

I have tried numerous configurations in the postgresql.conf file. I have
set the shared_buffers with values ranging from 75 MB to 4000MB with no
luck. I have also tried increasing the sort_mem with no luck.

When the test is running, the cpu is well over 50% idle and iostat shows
that the processes are not waiting for i/o and disk usage percentage is low.

Any help would be appreciated.

Thanks.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-03-10 20:07:54 Re: Large difference between elapsed time and run time for queries
Previous Message scott.marlowe 2003-03-10 16:55:53 Re: Index / Performance issues