Re: Todo: Teach planner to evaluate multiple windows in the optimal order

On Thu, Jan 26, 2023 at 9:11 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> I'm unsure if 69749243 might be partially to blame here as it favours
> single-key sorts. If you look at qsort_tuple_signed_compare(), you'll
> see that the tiebreak function will be called only when it's needed
> and there are > 1 sort keys. The comparetup function will re-compare
> the first key all over again. If I get some more time I'll run the
> tests again with the sort specialisation code disabled to see if the
> situation is the same or not.

> I've attached the benchmark script that I used and also a copy of the
> patch with a GUC added solely to allow easier benchmarking of patched
> vs unpatched.

I've attached a cleaned up v2 (*) of a patch to avoid rechecking the first
column if a specialized comparator already did so, and the results of the
"bench_windowsort" benchmark. Here, master vs. patch refers to skipping the
first column recheck, and on/off is the dev guc from David's last patch.

In my test, orderby_windowclause_pushdown caused 6 regressions by itself.

Not rechecking seems to eliminate the regression in 4 cases, and reduce it
in the other 2 cases. For those 2 cases (10e6 rows, random, mod 10 and
100), it might be worthwhile to "zoom in" with more measurements, but
haven't done that yet.

* v1 was here, but I thought it best to keep everything in the same thread,
and that thread is nominally about a different kind of specialization:

https://www.postgresql.org/message-id/CAFBsxsFdFpzyBekxxkiA4vXnLpw-wcaQXz%3DEAP4pzkZMo91-MA%40mail.gmail.com

--
John Naylor
EDB: http://www.enterprisedb.com