Re: Very specialised query

From: Matthew Wakeling
Subject: Re: Very specialised query
Date: ,
Msg-id: alpine.DEB.2.00.0904011802440.21772@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: Re: Very specialised query  (Віталій Тимчишин)
List: pgsql-performance

Tree view

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 by date:

From: david@lang.hm
Date:
Subject: Re: Raid 10 chunksize
From: Scott Marlowe
Date:
Subject: Re: Raid 10 chunksize