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

> On Oct 30, 2018, at 9:08 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Tue, 30 Oct 2018 at 07:58, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>
> 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.
>
> Anything left anchored would benefit, so SUBSTR(), TRIM() etc
>
> Main use for this would be where the partition condition is a function, so we can still order by partitions easily.

This would also be very helpful in many BI cases; it’s very common to aggregate based on year, year/month, year/quarter, etc.

The other thing that would be extremely useful would be pushing predicats through this, so you could do things like

WHERE date_trunc(‘year’, timestamp_field) = 2018