Обсуждение: Aggregate View and Conditions taking FOREVER
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.
illusina <illusina@hotmail.com> writes: > 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. Rather than guessing, how about showing EXPLAIN ANALYZE output for both cases? regards, tom lane