agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: agharta <agharta82@gmail.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Detect which sum of cartesian product (+ any combination of n records in tables) exceeds a value
Date: Fri, 20 Mar 2015 08:26:17 +0100
Message-ID: <550BCB99.3050901@gmail.com> (raw)
In-Reply-To: <CAKFQuwY27=A_Y3HjndKhAQzZtg4Y3wAHfDCXvEiHL6fMciq_ng@mail.gmail.com>
References: <550AF9A7.2090404@gmail.com>
<CAKFQuwY27=A_Y3HjndKhAQzZtg4Y3wAHfDCXvEiHL6fMciq_ng@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 03/19/2015 06:05 PM, David G. Johnston wrote:
>
> I likely would not be alive if I tried executing it on any
> non-trivial sized database though.
Me too :) !
>
> As an algorithm:
>
> Create two relations (temp tables/views/materialized views), one for
> t1/t2/t3 and one for t4/t4 each having a single row for every
> potential combination of rows. Each table would contribute two
> values, the content of "field_1" and the primary key of the
> corresponding table. The new PK would be a composite of all the
> contributing PKs
>
> For each relation, if the sum of the value columns is > 35 then every
> single row from the other table will provide a match. This is your
> first output.
>
> Cross Join the two relations, after removing those in each that were
> matched above, and sum together all 5 fields. This is your second output.
>
> Union All the two outputs together and you have your result.
>
> It can be done in one step but this at least gives you a prayer of
> executing in reasonable time for meaningfully sized datasets. You can
> just write the second part and avoid the union until your data
> warrants the more complex, but likely faster, setup.
>
> David J.
You're right, this should be the fastest implementation possible, but
cross/cartesian matching is very slow with a huge amount of data (it is
natural).
I think that a simple & dynamic (t4/t4/t4/t4... n times) solution is not
possible, as 9.4 PG version. Correct me if i am wrong.
I hoped that there was a magic-trick-function that would resolve the
problem. Nope. :(
I need to review & rewrite my db/application to solve the problem in
another way.
I owe you a beer, thanks a lot for your suggestions.
Cheers,
Agharta
view thread (4+ messages) latest in thread
Message-ID: <550BCB99.3050901@gmail.com>
Permalink: ../550BCB99.3050901@gmail.com/
Also on: postgresql.org/message-id/550BCB99.3050901@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-sql@postgresql.org
Cc: agharta82@gmail.com
Subject: Re: Detect which sum of cartesian product (+ any combination of n records in tables) exceeds a value
In-Reply-To: <550BCB99.3050901@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox