Matthew Wakeling <matthew@xxxxxxxxxxx> writes: > This query takes about two hours. > Now, it happens that there is an algorithm for calculating overlaps which > is really quick. It involves iterating through the table in order of the > start variable and keeping a list of ranges which "haven't ended yet". > When you read the next range from the table, you firstly purge all the > ranges from the list that end before the beginning of the new range. Then, > you output a result row for each element in the list combined with the new > range, then you add the new range to the list. > This algorithm just doesn't seem to fit into SQL at all. No, it doesn't. Have you thought about coding it in plpgsql? I have a feeling that it might be possible to do it using SQL:2003 recursive queries, but the procedural coding is likely to be easier to understand and better-performing. Not to mention that you won't have to wait for 8.4... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
