Re: Very specialised query

Список
Период
Сортировка
От Tom Lane
Тема Re: Very specialised query
Дата
Msg-id 22529.1238082582@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Very specialised query  (Matthew Wakeling)
Ответы Re: Very specialised query  (Matthew Wakeling)
Список pgsql-performance
Дерево обсуждения
Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  ("Kevin Grittner", )
 Re: Very specialised query  (Tom Lane, )
  Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Matthew Wakeling, )
  Re: Very specialised query  (Tom Lane, )
 Re: Very specialised query  (Віталій Тимчишин, )
  Re: Very specialised query  (Matthew Wakeling, )
   Re: Very specialised query  (Tom Lane, )
    Re: Very specialised query  (Matthew Wakeling, )
    Re: Very specialised query  (Matthew Wakeling, )
     Re: Very specialised query  (Віталій Тимчишин, )
      Re: Very specialised query  (Matthew Wakeling, )
       Re: Very specialised query  (Віталій Тимчишин, )
        Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Dimitri Fontaine, )
  Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  ("Marc Mamin", )
  Re: Very specialised query  (Matthew Wakeling, )
   Re: Very specialised query  ("Marc Mamin", )
    Re: Very specialised query  (Matthew Wakeling, )
 Re: Very specialised query  (Matthew Wakeling, )
  Re: Very specialised query  (Віталій Тимчишин, )
   Re: Very specialised query  (Matthew Wakeling, )
    Re: Very specialised query  (Matthew Wakeling, )
     Re: Very specialised query  (Matthew Wakeling, )
      Re: Very specialised query  (Craig Ringer, )
 Re: Very specialised query  ("Marc Mamin", )
  Re: Very specialised query  (Matthew Wakeling, )
Matthew Wakeling <> 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Scott Carey
Дата:
Сообщение: Re: Raid 10 chunksize
Следующее
От: Scott Carey
Дата:
Сообщение: Re: Raid 10 chunksize