Re: [RFC] ASOF Join

On 21.11.2021 07:53, Ilya Anfimov wrote:
> DISCLAIMER:  I  am both seeing this first time and I don't have a
> good understanding of the PosgreSQL development practices.

> pure evil
> ridiculous
No worries, at least you got the etiquette just right.


There are two points in your mail that I'd like to discuss. First, the ASOF grammar being bad because it's implicit. I
doagree on the general idea that explicit is better UX than implicit, especially when we're talking about SQL where you
spendhalf the time battling the query planner already. However, in the grammar I proposed it's unambiguous which
conditionsare ASOF and which are not -- all inequalities are ASOF, all equalities are not, and there can be no other
kindsof conditions for this type of join. It can also support any number of ASOF conditions. Which grammar exactly do
yousuggest? Maybe something like this:
 

asks JOIN bids ON asks.instrument = bids.instrument ASOF asks.timestamp <= bids.timestamp

This still does require a keyword.


Second, you say that we must first optimize the corresponding LATERAL. I was thinking about this as well, but _that_ is
what'snot explicit. I'm not sure if this optimization would have any value outside of optimizing ASOF joins. We might
givebetter UX if we embrace the fact that we're doing an ASOF join and allow the user to state this explicitly and get
anefficient and predictable plan, or an error, instead of trying to guess this from the rewritten queries and silently
fallingback to an inefficient plan for cryptic reasons.
 


--
Alexander Kuzmenkov
Timescale