Re: crosstab speed

Jeremiah Elliott wrote:
> ok, I have an application that I am trying to speed up. Its a reporting
> application that makes heavy use of the crosstab function.

<snip>

> here is an example query

>
> The crostab function is taking between 5 and 15 seconds to return.

Please run the two embedded queries independently, i.e.

select
ARRAY[site::text,product_line_description::text,report_sls::text,fy_period::text]
as COL_HEADER, fy_year, sum(invoice_value) from order_data_tbl
where fy_year is not null group by
site::text,product_line_description::text,report_sls::text,fy_period::text,
fy_year order by
site::text,product_line_description::text,report_sls::text,fy_period::text;

-- and --

select fy_year from order_data_tbl
where fy_year is not null
group by fy_year
order by fy_year;

How long does each take? crosstab cannot run any faster than the sum of
these two queries run on their own.

If the second one doesn't change often, can you pre-calculate it,
perhaps once a day?

Joe