Optimising compactify_tuples()
Thomas Munro <thomas.munro@gmail.com>
From: Thomas Munro <thomas.munro@gmail.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2020-08-17T11:00:34Z
Lists: pgsql-hackers
Attachments
- 0001-Add-parameterized-sorting-searching-support.patch (text/x-patch) patch 0001
- 0002-Use-specialized-sort-in-compactify_tuples.patch (text/x-patch) patch 0002
Hi, With [1] applied so that you can get crash recovery to be CPU bound with a pgbench workload, we spend an awful lot of time in qsort(), called from compactify_tuples(). I tried replacing that with a specialised sort, and I got my test crash recovery time from ~55.5s down to ~49.5s quite consistently. I've attached a draft patch. The sort_utils.h thing (which I've proposed before in another context where it didn't turn out to be needed) probably needs better naming, and a few more parameterisations so that it could entirely replace the existing copies of the algorithm rather than adding yet one more. The header also contains some more related algorithms that don't have a user right now; maybe I should remove them. While writing this email, I checked the archives and discovered that a couple of other people have complained about this hot spot before and proposed faster sorts already[2][3], and then there was a wide ranging discussion of various options which ultimately seemed to conclude that we should do what I'm now proposing ... and then it stalled. The present work is independent; I wrote this for some other sorting problem, and then tried it out here when perf told me that it was the next thing to fix to make recovery go faster. So I guess what I'm really providing here is the compelling workload and numbers that were perhaps missing from that earlier thread, but I'm open to other solutions too. [1] https://commitfest.postgresql.org/29/2669/ [2] https://www.postgresql.org/message-id/flat/3c6ff1d3a2ff429ee80d0031e6c69cb7%40postgrespro.ru [3] https://www.postgresql.org/message-id/flat/546B89DE.7030906%40vmware.com
Commits
-
Optimize compactify_tuples function
- 19c60ad69a91 14.0 landed
-
Report resource usage at the end of recovery
- 10a5b35a0061 14.0 landed
-
Allow incremental sorts for windowing functions
- 62e221e1c01e 14.0 cited