Re: Finding latest record for a number of groups in an INSERT-only table

On Tue, Jul 5, 2011 at 12:32 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> I think its a pretty common requirement and we should be looking to
> optimize it if it isn't handled well.

I agree; although I wanted to be sure that it is not in fact handled
well by some mechanism I haven't seen yet.

> The only problem is that there is a few ways of writing that in SQL
> and we would probably need to recognise how to transform between query
> types to achieve a common form.

I think that'd be good for completeness, although I also think that
having a 'design pattern' of sorts for dealing with this workload
would be totally acceptable for quite some time, even if
canonicalization from other forms is technically possible

> For example, the post you cite uses a correlated subquery whereas its
> possible to write it using an IN subselect.

I liked the brevity of DISTINCT ON form, even if semantically it feels
a little weird, yet, I think the biggest bang for the buck is looking
at the "row_number() = N" form of the problem: I've seen people do
this a number of times for different reasons. In my case N = 1, but
if I wanted to take an average of a metric of three records or so then
one could easily imagine wanting row_number() >= 3, followed by
aggregations. I've seen some other real-world use cases fly by where
people wanted some N where N != 1, too. When such a predicate is
around, one can be sure that no more than N rows are generated per
partition, and if there's an appropriate index then some kind of
costing can take place to see if it's worth using.

Interestingly, this may also be a way to (in a degenerate case)
acquire a path to enable skip scan, but last I checked skip scan was
problematic somehow for other reasons, which makes me wonder how
feasible it'd be to embark on this optimizer enhancement, should it
prove necessary.

--
fdr