Re: B-Tree support function number 3 (strxfrm() optimization)

On Mon, Sep 29, 2014 at 10:34 PM, Peter Geoghegan <pg@heroku.com> wrote:
> <single sortsupport state patch>.

You probably noticed that I posted an independently useful patch to
make all tuplesort cases use sortsupport [1] - currently, both the
B-Tree and CLUSTER cases do not use the sortsupport infrastructure
more or less for no good reason. That patch was primarily written to
make abbreviated keys work for all cases, though. I think that making
heap tuple sorts based on a text attribute much faster is a very nice
thing, but in practice most OLTP or web applications are not all that
sensitive to the amount of time taken to sort heap tuples. However,
the length of time it takes to build indexes is something that most
busy production applications are very sensitive to, since of course
apart from the large consumption of system resources often required,
however long the sort takes is virtually the same amount of time as
however long we hold a very heavy, disruptive relation-level
ShareLock. Obviously the same applies to CLUSTER, but more so, since
it must acquire an AccessExclusiveLock on the relation to be
reorganized. I think almost everyone will agree that making B-Tree
builds much faster is the really compelling case here, because that's
where slow sorts cause by far the most pain for users in the real
world.

Attached patch, when applied, accelerates all tuplesort cases using
abbreviated keys, building on previous work here, as well as the patch
posted to that other thread. Exact instructions are in the commit
message of 0004-* (i.e. where to find the pieces I haven't posted
here). I also attach a minor bitrot fix commit/patch.

Performance is improved for B-Tree index builds by a great deal, too.
The improvements are only slightly less than those seen for comparable
heap tuple sorts (that is, my earlier test cases that had client
overhead removed). With larger sorts, that difference tends to get
lost in the noise easily.

I'm very encouraged by this. I think that being able to build B-Tree
indexes on text attributes very significantly faster than previous
versions of PostgreSQL is likely to be a significant feature for
PostgreSQL 9.5. After all, external sorts are where improvements are
most noticeable [2] - they're so much faster with this patch that
they're actually sometimes faster than internal sorts *with*
abbreviated keys. This would something that I found quite surprising.

[1] http://www.postgresql.org/message-id/CAM3SWZTfKZHTUiWDdHg+6tcYuMsdHoC=bMuAiVgMP9AThj42Gw@mail.gmail.com
[2] http://www.postgresql.org/message-id/CAM3SWZQVjCgmE6uBe-YDipu0n5BO7RMz31zRHMSkdDuynejmJA@mail.gmail.com
--
Peter Geoghegan