Re: Query to find contiguous ranges on a column

On Tue, Oct 13, 2009 at 5:12 PM, Tim Landscheidt <tim(at)tim-landscheidt(dot)de> wrote:
> Peter Hunsberger <peter(dot)hunsberger(at)gmail(dot)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