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