pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (19+ messages)  latest in thread

Message-ID: <1dc7f0e305012712024c1c8d30@mail.gmail.com>
Permalink:  ../1dc7f0e305012712024c1c8d30@mail.gmail.com/
Also on:    postgresql.org/message-id/1dc7f0e305012712024c1c8d30@mail.gmail.com

 · 

reply

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