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 do agree on the general idea that explicit is better UX than implicit, especially when we're talking about SQL where you spend half the time battling the query planner already. However, in the grammar I proposed it's unambiguous which conditions are ASOF and which are not -- all inequalities are ASOF, all equalities are not, and there can be no other kinds of conditions for this type of join. It can also support any number of ASOF conditions. Which grammar exactly do you suggest? 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's not explicit. I'm not sure if this optimization would have any value outside of optimizing ASOF joins. We might give better UX if we embrace the fact that we're doing an ASOF join and allow the user to state this explicitly and get an efficient and predictable plan, or an error, instead of trying to guess this from the rewritten queries and silently falling back to an inefficient plan for cryptic reasons.

--
Alexander Kuzmenkov
Timescale