Re: Super PathKeys (Allowing sort order through precision loss functions)

David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> I've started working on something I've ended up calling "Super
> PathKeys". The idea here is to increase the likelihood of a Path with
> PathKeys being used for a purpose that requires a less strict sort
> order due to ordering being required from the return value of some
> precision loss function.

I'm a little confused by the terminology here, or why this has anything
at all to do with a new sort of pathkey. It seems like the idea you're
driving at is to be able to mark a function as being order-preserving,
in the sense that if one input is known sorted then the output will
also be sorted (by the same or a related opclass). You probably need
some additional constraints, like any other inputs being constants,
before that really works. But given that, I don't see why you need a
new kind of pathkey: you just have a new way to conclude that some
path is sorted by the pathkey you already want.

Maybe if I read the patch it'd be clearer why you want to describe it
that way, but I'm too lazy to do that right now. One thing I would
say though is that a pg_proc column identifying the interesting input
parameter is insufficient; you'd need to *explicitly* say which opclass(es)
the sorting behavior guarantee applies for.

> -- Test a more complex case where the superkey can be matched to
> multiple pathkeys
> explain (costs off) select date_trunc('year', ts), date_trunc('month',
> ts), a, count(*) from tstbl group by 1,2,3 order by 1,2,3;
> QUERY PLAN
> -----------------------------------------------------------------------------
> GroupAggregate
> Group Key: date_trunc('year'::text, ts), date_trunc('month'::text, ts), a
> -> Index Only Scan using tstbl_ts_a_idx on tstbl
> (3 rows)

[ squint... ] Does that really work? If so, how? It would need a whole
lot more knowledge about the behavior of date_trunc than I think could
possibly be reasonable to encode in a general mechanism.

regards, tom lane