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: (view raw, whole thread or download thread mbox)
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.


> 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.

as COL_HEADER, fy_year, sum(invoice_value) from order_data_tbl 
            where  fy_year is not null group by 
fy_year order by 

-- 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?


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-2017 The PostgreSQL Global Development Group