Re: Very specialised query

Matthew Wakeling <matthew(at)flymine(dot)org> writes:
> 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)

> Postgres estimates these two index scans to be equivalent in cost, where
> they are actually vastly different in real cost. Shouldn't Postgres favour
> a "between" index scan over an open-ended one?

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?

regards, tom lane