pgsql-sql-ow...@postgresql.org wrote:
Date: Thu, 25 Jun 2009 17:13:42 +0100
From: "Oliveiros Cristina" <oliveiros.crist...@marktest.pt>
To: "Rob Sargent" <robjsarg...@gmail.com>,
<pgsql-sql@postgresql.org>
Subject: Re: Bucketing Row Data in columns
Message-ID: <02e701c9f5af$e8cb4f90$ec5a3...@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" <robjsarg...@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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
