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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
