Re: Why percent_rank is so slower than rank?

Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> writes:
> Hm? Once percent_rank() scans to the partition end, any other window
> functions that scans row by row don't need to care the memory
> reduction, aren't they? Or more generally, if the partition was
> scanned to the end, we don't need to trim tuplestore anymore. Am I
> misunderstanding?

Giving back the memory as we do the scan is still a good thing IMO;
there might be other uses for it. In any case I don't see where you're
going to put such a heuristic without breaking potentially interesting
uses elsewhere. The tuplestore doesn't know anything about partitions
being read to the end; and WindowAgg doesn't (or shouldn't) know about
whether the tuplestore is all in memory.

Furthermore, the performance problem would exist for any situation where
the window functions had read far beyond the frame start, whether that
was all the way to partition end or not. Consider a frame like ROWS
BETWEEN 10000 PRECEDING AND 10000 FOLLOWING.

In the end this is a local problem inside tuplestore, and kluging its
callers to work around it is the wrong approach.

regards, tom lane