On Tue, Oct 13, 2009 at 5:12 PM, Tim Landscheidt <tim@tim-landscheidt.de> wrote:
> Peter Hunsberger <peter.hunsberger@gmail.com> wrote:
>
> You can either use a PL/pgSQL function ("SETOF TEXT" just
> for the convenience of the example):
That works well, takes about 20 seconds to do the 6M+ rows
>
> or a recursive query (which I always find very hard to com-
> prehend):
>
> | WITH RECURSIVE RecCols (LeftBoundary, Value) AS
> | (SELECT col, col FROM t WHERE (col - 1) NOT IN (SELECT col FROM t)
> | UNION ALL SELECT p.LeftBoundary, c.col FROM RecCols AS p, t AS c WHERE c.col = p.Value + 1)
> | SELECT LeftBoundary, MAX(Value) AS RightBoundary FROM RecCols
> | GROUP BY LeftBoundary
> | ORDER BY LeftBoundary;
>
> Could you run both against your data set and find out which
> one is faster for your six million rows?
>
Turns out the server is v 8.3, looks like I need to get them to
upgrade it so I get recursive and windowing :-(. If this happens any
time soon I'll let you know the results.
Many thanks.
--
Peter Hunsberger
