Re: Detect which sum of cartesian product (+ any combination of n records in tables) exceeds a value

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