Re: Cannot reproduce why a query is slow

On Thu, May 05, 2011 at 09:27:47AM -0700, John Cheng wrote:

> I have a couple of queries that allow me to see the active locks in
> the database. It might help me see if these queries are blocked by
> other locking queries.

Yes. The pg_locks view is your friend here.

> In terms of IO limits, there are no other reports that are running.
> What is the appropriate way to see if IO is the issue? I think the
> 900ms time is due to the database fetching data from disk. Can I force
> the command line version to not use the memory cache and see if it
> takes around 900ms in that case?

No, but you could run iostat during the period that this is happening
and see whether it shows you a big spike at that time. Also, the
pg_statio_user_tables data might help you. I think to make useful
inferences, you'd probably have to clear the statistics before and
after this runs, but you could do that and then compare what you get
when you look at it by hand.

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca