Re: postgres overall performance seems to degrade when large SELECT are requested

> The problem seem to arise when a SELECT that returns a lot of rows is

Does the SELECT return a lot of rows, or does it scan a lot of rows ?
(for instance, if you use aggregates, it might scan lots of data but only
return few rows).

> The problem is that when the SELECTs are run the main application starts
> running out of available connections which means that postgres is not
> returning the query results fast enough. What I find a little bit
> starnge is that the report engine's SELECTs operate on a different set
> of tables than the ones the main application is using. Also the db box
> is hardly breaking a sweat, CPU and memory utilization are ridiculously
> low and IOwaits are typically less than 10%.

Is it swapping ? (vmstat -> si/so)
Is it locking ? (probably not from what you say)
Is the network connection between the client and DB server saturated ?
(easy with 100 Mbps connections, SELECT with a large result set will
happily blast your LAN)
Is the reporting tool running on the same machine as the DB client and
killing it ? (swapping, etc)

If it's a saturated network, solutions are :
- install Gb ethernet
- run the report on the database server (no bandwidth problems...)
- rewrite the reporting tool to use SQL aggregates to transfer less data
over the network
- or use a cursor to fetch your results in chunks, and wait a little
between chunks

> Has anyone experienced this?

Yeah on benchmarks sometimes the LAN gave up before Postgres broke a
sweat... Gb ethernet solved that...

> Are there any settings I can change to improve throughput? Any help
> will be greatly appreciated.

iptraf will tell you all about your network traffic
vmstat will tell you if your server or client is io-cpu-swap bound
you'd need to post output from those...

>
>
> Thanks,
> val
>
>
> __________________________________________________________
> Sent from Yahoo! Mail.
> A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html
>