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

On 17/05/2020 at 04:22, Tom Lane wrote:> 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 :-(

Thanks, I didn't realise my version of the query was incorrect.

It seems like there isn't much hope of creating a view equivalent to the
query which would behave reasonably with "date >= CONST" constraint, or
am I missing something? I think I can create a workaround using
functions but would love to know if there's something simpler I could do.

Thanks,
Lukasz