Subject: Re: Recursive merging of overlapping arrays in a column
To: pgsql-sql@postgresql.org
References: <1442747556700-5866560.post@n5.nabble.com>
From: Rob Sargent <robjsargent@gmail.com>
Message-ID: <55FEBBE0.8090902@gmail.com>
Date: Sun, 20 Sep 2015 08:00:00 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.2.0
MIME-Version: 1.0
In-Reply-To: <1442747556700-5866560.post@n5.nabble.com>
Content-Type: multipart/alternative;
 boundary="------------090506010307000801020103"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------090506010307000801020103
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

On 09/20/2015 05:12 AM, dave wrote:
> Hey mailing list,
>
> i have the following Table:
>
>
>
> I want to merge the arrays which have overlapping elements, so that I get
> the result which doesn't contain overlapping arrays anymore:
>
>
> I am not an expert in SQL and it took me a long time to come up with this
> solution:
>
>
> Which gives me the result:
>
>
> Result number 3 is contained in number one and shouldn't be in the output
> anymore, because I only want non overlapping arrays in the result.
>
> Another problem I encountered is that the performance of this query seems to
> be very bad. I tried running it on a larger table (~400000 arrays) and it is
> still running after ~10h.
>
> I would appreciate any input on this problems, so it would be nice if anyone
> could give me a hint how to get only the merged arrays without overlaps in
> the resultset and maybe how to build a more elegant and efficient query.
>
>
> Thanks in advance,
>
> Dave
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/Recursive-merging-of-overlapping-arrays-in-a-column-tp5866560.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
I don't see your table def, sql nor result output. Suggest you re-post 
with all those as plain text

--------------090506010307000801020103
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=windows-1252"
      http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 09/20/2015 05:12 AM, dave wrote:<br>
    </div>
    <blockquote cite="mid:1442747556700-5866560.post@n5.nabble.com"
      type="cite">
      <pre wrap="">Hey mailing list,

i have the following Table:


I want to merge the arrays which have overlapping elements, so that I get
the result which doesn't contain overlapping arrays anymore:


I am not an expert in SQL and it took me a long time to come up with this
solution:


Which gives me the result:


Result number 3 is contained in number one and shouldn't be in the output
anymore, because I only want non overlapping arrays in the result.

Another problem I encountered is that the performance of this query seems to
be very bad. I tried running it on a larger table (~400000 arrays) and it is
still running after ~10h.

I would appreciate any input on this problems, so it would be nice if anyone
could give me a hint how to get only the merged arrays without overlaps in
the resultset and maybe how to build a more elegant and efficient query.


Thanks in advance,

Dave


--
View this message in context: <a class="moz-txt-link-freetext" href="http://postgresql.nabble.com/Recursive-merging-of-overlapping-arrays-in-a-column-tp5866560.html">http://postgresql.nabble.com/Recursive-merging-of-overlapping-arrays-in-a-column-tp5866560.html</a>
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


</pre>
    </blockquote>
    <font face="Courier New, Courier, monospace">I don't see your table
      def, sql nor result output. Suggest you re-post with all those as
      plain text</font><br>
  </body>
</html>

--------------090506010307000801020103--