Skip site navigation (1) Skip section navigation (2)

Re: crosstab speed

From: Joe Conway <mail(at)joeconway(dot)com>
To: Jeremiah Elliott <jeremiah0(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: crosstab speed
Date: 2008-11-13 22:06:19
Message-ID: 491CA4DB.6000606@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Patrice BeliveauDate: 2008-11-14 16:14:18
Subject: Difference in query plan
Previous:From: Scott MarloweDate: 2008-11-13 21:41:07
Subject: Re: crosstab speed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group