Re: slow queries, possibly disk io

On 5/26/05, Josh Close <narshe(at)gmail(dot)com> wrote:
> I have some queries that have significan't slowed down in the last
> couple days. It's gone from 10 seconds to over 2 mins.
>
> The cpu has never gone over 35% in the servers lifetime, but the load
> average is over 8.0 right now. I'm assuming this is probably due to
> disk io.
>
> I need some help setting up postgres so that it doesn't need to go to
> disk. I think the shared_buffers and effective_cache_size values are
> the one's I need to look at.

Few "mandatory" questions:

1. Do you vacuum your db on regular basis? :)

2. Perhaps statistics for tables in question are out of date, did you
try alter table set statistics?

3. explain analyze of the slow query?

4. if you for some reason cannot give explain analyze, please try to
describe the type of query (what kind of join(s)) and amount of data
found in the tables.

2 minutes from 10 seconds is a huge leap, and it may mean that
PostgreSQL for some reason is not planning as well as it could.
Throwing more RAM at the problem can help, but it would be better
to hint the planner to do the right thing. It may be a good time to
play with planner variables. :)

Regards,
Dawid