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

pgsql-sql-owner(at)postgresql(dot)org wrote:
> Date: Thu, 25 Jun 2009 17:13:42 +0100
> From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
> To: "Rob Sargent" <robjsargent(at)gmail(dot)com>,
> <pgsql-sql(at)postgresql(dot)org>
> Subject: Re: Bucketing Row Data in columns
> Message-ID: <02e701c9f5af$e8cb4f90$ec5a3d0a(at)marktestcr(dot)marktest(dot)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(at)gmail(dot)com>
> To: <pgsql-sql(at)postgresql(dot)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