Search Postgresql Archives

Re: Query to find contiguous ranges on a column

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Oct 13, 2009 at 5:12 PM, Tim Landscheidt <tim@xxxxxxxxxxxxxxxxxx> wrote:
> Peter Hunsberger <peter.hunsberger@xxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux