Re: Cannot reproduce why a query is slow

On Thu, May 5, 2011 at 8:54 AM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:
> On Thu, May 05, 2011 at 08:02:46AM -0700, John Cheng wrote:
>
>> We have certain types of query that seems to take about 900ms to run
>> according to postgres logs. When I try to run the same query via
>> command line with "EXPLAIN ANALYZE", the query finishes very quickly.
>
> Just a couple ideas.
>
> First, when you do this via command line, presumably the conditions
> that set up the query aren't present.  Is it possible that there's
> been a lot of activity on the table leading to dead rows that have
> been cleaned up by autovacuum by the time you come along?  (Or that
> the table has otherwise changed so that you are getting the benefit of
> indexes that the query wasn't using?)
>
> I especially note that
>
>> WHERE ((lm.reporting_date >= '2011-04-05') AND (lm.reporting_date <=
>> '2011-05-05')
>>     AND (lrd.dealer_region = 'SO') AND (lrd.dealer_area = '02')
>>     AND (lm.lead_id < 2645059)
>>     AND (lrd.processing_state <> 'REJECTED') AND
>> ((lrd.processing_state = 'NEW') OR (lrd.processing_state =
>> 'PROCESSING') OR (lrd.processing_state = 'DELIVER') OR
>> (lrd.processing_state = 'DELIVERED') OR (lrd.processing_state =
>> 'DONE') OR (lrd.processing_state = 'ERROR'))
>
> these all look like the sort of status values that might change as the
> result of batch operations.
>
> Similarly, you might be running into I/O limits.  If this is a large
> report that is running at the same time as batch loads and so on of
> updates, you can find the query is very slow just because the machine
> is busy.
>
> Finally, you're not standing in line behind any locks, are you?
>
> Anyway, those are the things I'd start with.
>
> A
>
> --
> Andrew Sullivan
> ajs(at)crankycanuck(dot)ca

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.

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?

--
---
John L Cheng