From: | Tim Landscheidt <tim(at)tim-landscheidt(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Query to find contiguous ranges on a column |
Date: | 2009-10-14 21:50:12 |
Message-ID: | m3fx9l4pd7.fsf@passepartout.tim-landscheidt.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Peter Hunsberger <peter(dot)hunsberger(at)gmail(dot)com> wrote:
> [...]
>> 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.
After some tests with a data set of 7983 rows (and 1638 ran-
ges): Don't! :-) The recursive solution seems to be more
than double as slow as the iterative. I'll take it to -per-
formance.
Tim
From | Date | Subject | |
---|---|---|---|
Next Message | Naoko Reeves | 2009-10-14 21:59:29 | how to Export ALL plpgsql functions/triggers to file |
Previous Message | Adrian Klaver | 2009-10-14 21:24:04 | Re: could not open process token: error code 5 |