Re: Using b-tree index for >= condition when joining - Mailing list pgsql-general

From Łukasz Dąbek
Subject Re: Using b-tree index for >= condition when joining
Date
Msg-id b0d75b33-2006-15e3-047c-4487520b06cd@gmail.com
Whole thread Raw
In response to Re: Using b-tree index for >= condition when joining  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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



pgsql-general by date:

Previous
From: "Weatherby,Gerard"
Date:
Subject: NFS version for replication
Next
From: James Sewell
Date:
Subject: Re: Inherited an 18TB DB & need to backup