Re: Very specialised query

Список
Период
Сортировка
От Matthew Wakeling
Тема Re: Very specialised query
Дата
Msg-id alpine.DEB.2.00.0904011811360.21772@aragorn.flymine.org
обсуждение исходный текст
Ответ на Re: Very specialised query  (Віталій Тимчишин)
Ответы 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, )
On Mon, 30 Mar 2009, Віталій Тимчишин wrote:
> What is the bad plan? Is it like the first plan from your first message?

It's the plan a few messages back. The UNION ALL query I showed
effectively got the database to do it both ways round.

It's the case that a "between" index scan will return much fewer rows than
an open-ended index scan.

> BTW: About aggregates: they can return arrays, but I can't imagine what you can group by on... May be windowing
functionsfrom 8.4 
> could help.

A normal function seems the best way to go about this - they can return
multiple rows.

So, I have written a plpgsql function to calculate overlaps. It works
reasonably quickly where there aren't that many overlaps. However, it
seems to go very slowly when there are a large number of rows to return. I
am considering recoding it as a C function instead.

1. The docs say that returning multiple rows from plpgsql waits until the
     whole lot are done before returning any. Does this happen with the C
     functions too?
2. What sort of speedup would I be likely to see?
3. How do I RAISE NOTICE in a C function?

> Also, if your maximum length (select max(end-start) from location) is low enough, you can try adding some more
constraintsto make 
> optimizer happy (have it more precise row count to select correct plan).

Alas:

select min(start), max(start), min(end), max(end), max(end - start) from location;

  min |   max    | min |   max    |   max
-----+----------+-----+----------+----------
    1 | 61544858 |   1 | 61545105 | 21512431
(1 row)

Matthew

--
 I suppose some of you have done a Continuous Maths course. Yes? Continuous
 Maths? <menacing stares from audience> Whoah, it was like that, was it!
                                        -- Computer Science Lecturer

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

Предыдущее
От: david@lang.hm
Дата:
Сообщение: Re: Raid 10 chunksize
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: Raid 10 chunksize