From: Alexandre Leclerc <alexandre.leclerc@gmail.com>
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: PERFORM <pgsql-performance@postgresql.org>
Subject: Re: Flattening a kind of 'dynamic' table
Date: Thu, 27 Jan 2005 15:02:48 -0500
Message-ID: <1dc7f0e305012712024c1c8d30@mail.gmail.com> (raw)
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3412A75F0@Herge.rcsinc.local>
References: <6EE64EF3AB31D5448D0007DD34EEB3412A75F0@Herge.rcsinc.local>
On Thu, 27 Jan 2005 13:02:48 -0500, Merlin Moncure
<merlin.moncure@rcsonline.com> wrote:
> Alexandre wrote:
> > On Thu, 27 Jan 2005 10:44:45 -0500, Merlin Moncure
> > <merlin.moncure@rcsonline.com> wrote:
> > > Alexandre wrote:
> > > Let's start with the normalized result set.
> > >
> > > product_id | department_id | sum
> > > 924 a 6000
> > > 924 c 1575
> > > 907 a 1500
> > > [...]
> >
> Right. I expanding departments into columns is basically a dead end.
> First of all, SQL is not really designed to do this, and second of all
> (comments continued below)
Ok, I got it. The basic message is to avoid making columns out of rows
like I'm doing right now, that "de-normalizing" in an array is the way
to go. So I should query and get the results in an array then after my
application will parse the array into the good columns. (I'm
developping a software.)
If I still got it wrong, this is because the 'geek' section of my
brain is in vacation: leave a message and when it'll come back, it'll
explain all this to me! :)
So I found the array_accum function in the doc, so I did create it.
CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);
Then I created this new select:
SELECT
product_id,
array_accum(department_id) as a_department_id,
array_accum(req_time) as a_req_time
FROM (SELECT * FROM design.product_department_time) AS tmp
GROUP BY product_id;
It gives:
product_id | a_department_id | a_req_time
------------+-----------------+------------------
924 | {A,C} | {6000,1575}
907 | {A,C,D} | {1500,1500,4575}
906 | {A,C,D} | {3000,3000,1935}
So, the performance should be much better using this agregate approach?
No I thing I'll merge the results in my software, unless you think
that at this point doing a LEFT JOIN with my jobs table is the way to
go, beacuse the performance will be good. (Personally I don't know the
answer of this one.)
> If parsing an array string is a pain I happen to have a C++ class handy
> that can compose/decompose a postgresql array string if:
> a: no more than 1 dimension and
> b: array bounds are known
>
> Let me know if you need it and I'll send it over.
Thank you for your offer. I think parsing an array is the easiest
thing to do for me in all this. :) If I encounter any problem, I'll
drop you a mail.
Regards.
--
Alexandre Leclerc
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alexandre.leclerc@gmail.com, merlin.moncure@rcsonline.com
Subject: Re: Flattening a kind of 'dynamic' table
In-Reply-To: <1dc7f0e305012712024c1c8d30@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox