Re: Very specialised query

Список
Период
Сортировка
От Matthew Wakeling
Тема Re: Very specialised query
Дата
Msg-id alpine.DEB.2.00.0904011802440.21772@aragorn.flymine.org
обсуждение исходный текст
Ответ на Re: Very specialised query  (Віталій Тимчишин)
Список 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 Wed, 1 Apr 2009, Віталій Тимчишин wrote:
>       The outer nested join has the VALUES as the main loop, and the complicated join as the leaf. So, the
complicated
>       overlap-finding join gets run twice.
>
> That's weird. What do you have as statistics target? Planner is incorrect few orders of magnitude, so increasing it
mayhelp. 

Unfortunately, the statistics are skewed, so increasing the statistics
target won't help. The problem is this:

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

           avg          |   stddev_pop   | min |   max
-----------------------+----------------+-----+----------
  1716.7503512098150214 | 24935.63375733 |   1 | 61544858
(1 row)

>       Oh, there's also the great big sort and unique, but I think I can get rid of that.
>
>
> As far as I can see, duplicates will occur if and only if l1.start == l2.start && l1.end == l2.end.
> That can be easily filtered by adding "where n=1 or l1.start != l2.start or l1.end != l2.end" to outer select.

Close - duplicates will occur when l1.start == l2.start, so you filter
them out by adding "where n = 1 OR l1.start <> l2.start".

Matthew

--
 Lord grant me patience, and I want it NOW!

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

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