Re: Optimizing "top queries" ...

"Hans-Juergen Schoenig" <postgres(at)cybertec(dot)at> writes:

> i basically thought a node would make more sense as it gives some more
> flexibility. making the "replacement strategy" inside the node a bit more
> fancy this could actually open the door for further optimizations and for
> other operations.

I'm not sure what you mean by this. The two optimizations I saw were keeping
the top-N and keeping only distinct elements. You can also have both
simultaneously.

> also, OFFSET would be quite easy as the buffer size needed is perfectly
> defined by LIMIT + OFFSET. taking work_mem into consideration we could
> safely fall back to the old plan if too much data is fetched.

Certainly you have to keep the top-N where N = (limit + offset) or it won't
work correctly. That's what my patch did.

> can a node like that be of any further use for other operations as well? i am
> especially thinking of some other stuff related to analytics.

I think we'll need a whole slew of new nodes for implementing OLAP. Having the
top-N functionality inside tuplesort may indeed be useful for implementing
them.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com