Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 948B16500CD
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 13 Nov 2008 19:06:41 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 52619-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 13 Nov 2008 19:06:29 -0400 (AST)
X-Greylist: delayed 00:59:56.359165 by SQLgrey-1.7.6
Received: from joeconway.com (wsip-72-214-29-243.sd.sd.cox.net
 [72.214.29.243])
	by mail.postgresql.org (Postfix) with ESMTP id 889B864FE15
	for <pgsql-performance@postgresql.org>;
	Thu, 13 Nov 2008 19:06:27 -0400 (AST)
Received: from [206.190.70.130] (account jconway HELO [192.168.82.6])
	by joeconway.com (CommuniGate Pro SMTP 4.1.8)
	with ESMTP-TLS id 7179090; Thu, 13 Nov 2008 14:06:26 -0800
Message-ID: <491CA4DB.6000606@joeconway.com>
Date: Thu, 13 Nov 2008 14:06:19 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Thunderbird 2.0.0.14 (X11/20080501)
MIME-Version: 1.0
To: Jeremiah Elliott <jeremiah0@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: crosstab speed
References: <aa98dd100811131242r18325669m93c938382f3a7e28@mail.gmail.com>
In-Reply-To: <aa98dd100811131242r18325669m93c938382f3a7e28@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=RDNS_DYNAMIC=0.1
X-Spam-Level: 
X-Archive-Number: 200811/132
X-Sequence-Number: 31863

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