Re: Order-preserving function transforms and EquivalenceClass

Mat Arye <mat@timescaledb.com> writes:
> We are trying to extend/hook into the planner so that it understands that
> date_trunc('minute', time) has the same ordering as time (or rather that a
> sort ordering on the latter is always a valid sort ordering on the former).
> But this question really applies to any order-preserving transform such as
> (time+1) vs (time).

Hmm ... it seems like the thing that is missing from your statement of the
problem is the notion of one ordering being a refinement of another.  You
can't just say that "date_trunc('minute', time) has the same ordering as
time", or that for a float value x "floor(x) has the same ordering as x".
You can say that a data series ordered by x is also ordered by floor(x),
*but not vice versa*.  So "same" seems like the wrong word.  A related
concept that the planner does understand today is that a data series
ordered by x,y is also ordered by x ... but not vice versa.

The EquivalenceClass mechanism doesn't seem to me to be up to representing
such considerations.  Maybe it could be extended, but I think there's some
pretty fundamental design work needed here.  And I'm not sure it should be
that mechanism in particular: ECs are mostly about making transitivity
deductions, ie a=b and b=c implies a=c.  I'm not very sure what we'd want
an ordering-refinements notion to act like, but I'm pretty sure it's not
like equality.

Thinking about the "ORDER BY x,y" vs "ORDER BY x" precedent, I wonder
whether you could do something in the PathKey mechanism.  But PathKeys
currently depend on per-column EquivalenceClasses, so it's not real
obvious how to proceed there either.

If you've got ideas about what this should look like, I'm all ears.
But it's going to take some pretty basic work, you're not going to
just plug into already-existing mechanism.
        regards, tom lane