Re: [pgsql-sql] Daily digest v1.3050 (5 messages)

pgsql-sql-owner@postgresql.org wrote:
> Date: Thu, 25 Jun 2009 17:13:42 +0100
> From: "Oliveiros Cristina" <oliveiros.cristina@marktest.pt>
> To: "Rob Sargent" <robjsargent@gmail.com>,
>     <pgsql-sql@postgresql.org>
> Subject: Re: Bucketing Row Data in columns
> Message-ID: <02e701c9f5af$e8cb4f90$ec5a3d0a@marktestcr.marktest.pt>
>
> I admit that must be a more elegant and faster solution with pl/psql (or 
> whatever other languages)
>
> As I don't know nothing about pl/psql I tried with pure sql (if you don't 
> have a hunting dog, hunt with a cat)
>
> But obviously this solution doesn't scale well if you have a giant table 
> with lots of columns
>
> ----- Original Message ----- 
> From: "Rob Sargent" <robjsargent@gmail.com>
> To: <pgsql-sql@postgresql.org>
> Sent: Thursday, June 25, 2009 4:57 PM
> Subject: Re: [SQL] Bucketing Row Data in columns
>
>
>> >I would be suspicious of this sort of solution of turning rows into columns 
>> >by mean of a series of correlated sub-selects.  Once the data set gets 
>> >large and the number of columns goes over 2 or 3 this will in all 
>> >likelihood not perform well. I had the pleasure of re-writing a "report" 
>> >which was based on count() (similar to sum()) per user_id with the counts 
>> >going into various columns per user.  18000 users, a dozen columns from 
>> >table of 2 million rows, report took >1,000,000 seconds (yes almost 12 
>> >days) to complete.  Re-write runs in 5-10 minutes (now at 10M rows) by 
>> >getting the counts as rows (user, item, count) into a temp table and making 
>> >the columns from the temp table (pl/psql)  Getting the counts takes half 
>> >the time, making the flattened report takes half the time.
>> >
>>
Is it possible that using the "tablefunc" contrib module would help. 
What I mean is, couldn't this be written as a performant query that 
returns a set of rows and then use the crosstab capability to simply 
rewrite that rows as columns?

As another poster pointed out you can do the same with a set of CASE 
statements, but I wanted to throw this idea out there as well. I'm not 
too familiar with the the tablefunc / crosstab stuff, but it seems like 
this is generally what you're trying to accomplish?

I googled this fwiw: 
http://www.tek-tips.com/viewthread.cfm?qid=1444284&page=1

Best,

Steve