Background:
Ok..I've got a view which returns agency_id, fiscal_year, reporting_month,
count_col0, count_col1...grouping by agency_id, fiscal_year and
reporting_month. Now, if I just select * from myreport; it returns all the
various counts for the various fiscal years and all that within 4 seconds,
but if I add a conditional ie where fiscal_year = 2006 and reporting_month =
11, the query takes...10 minutes -- which I think is because the conditional
cols (ie fiscal_year) is calculated via extract(year from datecol) which I
think is causing that function to be checked on every row within the view.
Slow query:
select * from agency_9902_report_summary
where fiscal_year = 2006 and reporting_month = 11
Fast query:
select * from agency_9902_report_summary
Definition of fiscal_year/reporting_month:
...
extract(year from p.completed_timestamp) as fiscal_year,
extract(month from p.completed_timestamp) as reporting_month
...
Any help/tips are greatly appreciated...I've been working on this for a few
days with little success :|.
- Tyler
--
View this message in context:
http://www.nabble.com/Aggregate-View-and-Conditions-taking-FOREVER-tf4596962.html#a13124972
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
