Re: Very specialised query

On Fri, 27 Mar 2009, Tom Lane wrote:
>> Notice the two different index conditions:
>> (l1.end > l2.start) AND (l1.start < l2.start) - "between"
>> (l1.end > l2.start) AND (l1.start >= l2.start) - open-ended
>> Both have a cost of (cost=0.00..123.10 rows=4809 width=12)

> Currently the planner only notices that for a range check that involves
> comparisons of the same variable expression to two constants (or
> pseudoconstants anyway). In principle it might be reasonable to have a
> heuristic that reduces the estimated selectivity in the example above,
> but it looks to me like it'd make clauselist_selectivity() a lot slower
> and more complicated. When you see (l1.end > l2.start), how do you know
> which variable to try to match up against others? And if you try to
> match both, what do you do when you get matches for both?

Those two index conditions are on an index scan on the field l2.start.
Therefore, I would expect to only have to take any notice of l2.start when
working out selectivity on a range check for this particular plan. When
there is an index scan on a different field, then try and match that one
up instead.

Matthew

--