Re: Using b-tree index for >= condition when joining

=?UTF-8?B?xYF1a2FzeiBExIViZWs=?= <sznurek(at)gmail(dot)com> writes:
> I am having a problem with nudging postgres to choose a good plan for
> a query involving a left join and an inequality constraint on a column
> with b-tree index.
> ...
> It looks like the inequality on date isn't pushed down below the left
> join?

Nope. The planner only derives implied conditions from equality clauses.
There've been discussions about that in the past, but it was (and remains)
unclear that trying to account for other clause types would be a net win.
The planner-cycles-expended versus number-of-queries-improved tradeoff
doesn't look promising.

> I can get the plan I'd like to have by putting the same
> constraint on the date column on the second table:

Note that you're not really getting the same plan that way: it's not
a left join anymore, because you put a strict constraint on the join's
inner relation, so the planner realizes it doesn't have to produce any
null-extended rows. You could make it work with the desired semantics
with something along the lines of

SELECT * FROM tbl1 t1
LEFT JOIN (select * from tbl2 where tbl2.date >= '2019-04-21') t2
USING (date)
WHERE t1.date >= '2019-04-21';

but of course that's even less easy :-(

regards, tom lane