Re: Cannot reproduce why a query is slow

Dne 5.5.2011 17:02, John Cheng napsal(a):
> Hi,
> 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.
> What should I do to try to learn more about why it is running slowly?

I'd guess two possible causes - resource utilization and unexpected plan
changes.

Resource usually means there's too much I/O so the query is slow, but
when you try it later the drives are idle and query runs much faster.
Run some monitoring, e.g. even a simple 'iostat -x' or 'dstat' output
might be sufficient. Once the slow query happens, try to correlate it to
the CPU / disk activity.

The unexpected plan change is usually caused by autovacuum/autoanalyze
collecting skewed data for some reason, resulting in bad plan choice.
Then the autovacuum runs again and you get different (much better) plan.
This can be detected using the auto_explain contrib module, as someone
already recommended.

> The query is a bit complex, as it is generated by code, but I tried to
> format it for easier reading. I've also replaced actual data with fake
> data to protected personal information.

I generally do recommend using explain.depesz.com to post explain plans,
especially in case of complex queries. I've posted your query and this
is the result

http://explain.depesz.com/s/gJO

Not sure if it's relevant to your issue (probably not), but the bitmap
index scans are significantly overestimated. Not sure if the overlap
operator affects the estimate accuracy ...

BTW what postgresql version is this? How large the database is, how much
RAM is available? What is the size of shared_buffers?

regards
Tomas