Re: Very specialised query

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 may help.

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!