From: Joe Conway <mail@joeconway.com>
To: Jeremiah Elliott <jeremiah0@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: crosstab speed
Date: Thu, 13 Nov 2008 14:06:19 -0800
Message-ID: <491CA4DB.6000606@joeconway.com> (raw)
In-Reply-To: <aa98dd100811131242r18325669m93c938382f3a7e28@mail.gmail.com>
References: <aa98dd100811131242r18325669m93c938382f3a7e28@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mail@joeconway.com, jeremiah0@gmail.com
Subject: Re: crosstab speed
In-Reply-To: <491CA4DB.6000606@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox