agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: agharta <agharta82@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: 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:31:07 +0100
Message-ID: <550BCCBB.7070109@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)

Message-ID: <550BCCBB.7070109@gmail.com>
Permalink:  ../550BCCBB.7070109@gmail.com/
Also on:    postgresql.org/message-id/550BCCBB.7070109@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, david.g.johnston@gmail.com
  Subject: Re: Detect which sum of cartesian product (+ any combination of n records in tables) exceeds a value
  In-Reply-To: <550BCCBB.7070109@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