Content-Type: multipart/mixed; boundary="------------urSzx6WEup1B0YpKxXwh0Rf6"
Message-ID: <0ec3badc-7a35-4248-9f46-14a9ac049735@enterprisedb.com>
Date: Fri, 17 May 2024 21:41:10 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
To: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Content-Language: en-US
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Subject: allow sorted builds for btree_gist
Archived-At: <https://www.postgresql.org/message-id/0ec3badc-7a35-4248-9f46-14a9ac049735%40enterprisedb.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------urSzx6WEup1B0YpKxXwh0Rf6
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Hi,

I've been looking at GiST to see if there could be a good way to do
parallel builds - and there might be, if the opclass supports sorted
builds, because then we could parallelize the sort.

But then I noticed we support this mode only for point_ops, because
that's the only opclass with sortsupport procedure. Which mostly makes
sense, because types like geometries, polygons, ... do not have a well
defined order.

Still, we have btree_gist, and I don't think there's much reason to not
support sorted builds for those opclasses, where the gist opclass is
defined on top of btree ordering semantics.

So this patch does that, and the difference (compared to builds with
buiffering=on) can easily be an order of magnitude - at least that's
what my tests show:


test=# create index on test_int8 using gist (a) with (buffering = on);
CREATE INDEX
Time: 578799.450 ms (09:38.799)

test=# create index on test_int8 using gist (a) with (buffering = auto);
CREATE INDEX
Time: 53022.593 ms (00:53.023)


test=# create index on test_uuid using gist (a) with (buffering = on);
CREATE INDEX
Time: 39322.799 ms (00:39.323)

test=# create index on test_uuid using gist (a) with (buffering = auto);
CREATE INDEX
Time: 6466.341 ms (00:06.466)


The WIP patch adds enables this for data types with a usable sortsupport
procedure, which excludes time, timetz, cash, interval, bit, vbit, bool,
enum and macaddr8. I assume time, timetz and macaddr8 could be added,
it's just that I didn't find any existing sortsupport procedure. Same
for cash, but IIRC it's mostly deprecated.

Of course, people probably don't use btree_gist with a single column,
because they could just use btree. It's useful for multi-column GiST
indexes, with data types requiring GiST. And if the other opclasses also
allow sorted builds, this patch makes that possible. Of course, most
"proper GiST opclasses" don't support that, but why not - it's easy.

FWIW this is also why sorted builds likely are not a very practical way
to do parallel builds for GiST - it would help only with a small part of
cases, I think.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--------------urSzx6WEup1B0YpKxXwh0Rf6
Content-Type: text/x-patch; charset=UTF-8;
 name="v20240517-0001-allow-sorted-builds-for-btree_gist.patch"
Content-Disposition: attachment;
 filename="v20240517-0001-allow-sorted-builds-for-btree_gist.patch"
Content-Transfer-Encoding: base64

RnJvbSAwMTFlNmVlZTkyM2E2ZjUxNjY4ZjMyNzdmNDcwZDMyOTIyOTIzZDE3IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBUb21hcyBWb25kcmEgPHRvbWFzQDJuZHF1YWRyYW50
LmNvbT4KRGF0ZTogRnJpLCAxNyBNYXkgMjAyNCAyMDozNjowOCArMDIwMApTdWJqZWN0OiBb
UEFUQ0ggdjIwMjQwNTE3XSBhbGxvdyBzb3J0ZWQgYnVpbGRzIGZvciBidHJlZV9naXN0Cgot
LS0KIGNvbnRyaWIvYnRyZWVfZ2lzdC9NYWtlZmlsZSAgICAgICAgICAgICAgICAgfCAgMiAr
LQogY29udHJpYi9idHJlZV9naXN0L2J0cmVlX2dpc3QtLTEuOC0tMS45LnNxbCB8IDgyICsr
KysrKysrKysrKysrKysrKysrKwogY29udHJpYi9idHJlZV9naXN0L2J0cmVlX2dpc3QuY29u
dHJvbCAgICAgICB8ICAyICstCiAzIGZpbGVzIGNoYW5nZWQsIDg0IGluc2VydGlvbnMoKyks
IDIgZGVsZXRpb25zKC0pCiBjcmVhdGUgbW9kZSAxMDA2NDQgY29udHJpYi9idHJlZV9naXN0
L2J0cmVlX2dpc3QtLTEuOC0tMS45LnNxbAoKZGlmZiAtLWdpdCBhL2NvbnRyaWIvYnRyZWVf
Z2lzdC9NYWtlZmlsZSBiL2NvbnRyaWIvYnRyZWVfZ2lzdC9NYWtlZmlsZQppbmRleCA3YWMy
ZGYyNmMxMC4uNjgxOTBhYzVlNDYgMTAwNjQ0Ci0tLSBhL2NvbnRyaWIvYnRyZWVfZ2lzdC9N
YWtlZmlsZQorKysgYi9jb250cmliL2J0cmVlX2dpc3QvTWFrZWZpbGUKQEAgLTM0LDcgKzM0
LDcgQEAgREFUQSA9IGJ0cmVlX2dpc3QtLTEuMC0tMS4xLnNxbCBcCiAgICAgICAgYnRyZWVf
Z2lzdC0tMS4xLS0xLjIuc3FsIGJ0cmVlX2dpc3QtLTEuMi5zcWwgYnRyZWVfZ2lzdC0tMS4y
LS0xLjMuc3FsIFwKICAgICAgICBidHJlZV9naXN0LS0xLjMtLTEuNC5zcWwgYnRyZWVfZ2lz
dC0tMS40LS0xLjUuc3FsIFwKICAgICAgICBidHJlZV9naXN0LS0xLjUtLTEuNi5zcWwgYnRy
ZWVfZ2lzdC0tMS42LS0xLjcuc3FsIFwKLSAgICAgICBidHJlZV9naXN0LS0xLjctLTEuOC5z
cWwKKyAgICAgICBidHJlZV9naXN0LS0xLjctLTEuOC5zcWwgYnRyZWVfZ2lzdC0tMS44LS0x
Ljkuc3FsCiBQR0ZJTEVERVNDID0gImJ0cmVlX2dpc3QgLSBCLXRyZWUgZXF1aXZhbGVudCBH
aVNUIG9wZXJhdG9yIGNsYXNzZXMiCiAKIFJFR1JFU1MgPSBpbml0IGludDIgaW50NCBpbnQ4
IGZsb2F0NCBmbG9hdDggY2FzaCBvaWQgdGltZXN0YW1wIHRpbWVzdGFtcHR6IFwKZGlmZiAt
LWdpdCBhL2NvbnRyaWIvYnRyZWVfZ2lzdC9idHJlZV9naXN0LS0xLjgtLTEuOS5zcWwgYi9j
b250cmliL2J0cmVlX2dpc3QvYnRyZWVfZ2lzdC0tMS44LS0xLjkuc3FsCm5ldyBmaWxlIG1v
ZGUgMTAwNjQ0CmluZGV4IDAwMDAwMDAwMDAwLi4zZmQ2ZjMzZjQxYgotLS0gL2Rldi9udWxs
CisrKyBiL2NvbnRyaWIvYnRyZWVfZ2lzdC9idHJlZV9naXN0LS0xLjgtLTEuOS5zcWwKQEAg
LTAsMCArMSw4MiBAQAorLyogY29udHJpYi9idHJlZV9naXN0L2J0cmVlX2dpc3QtLTEuOC0t
MS45LnNxbCAqLworCistLSBjb21wbGFpbiBpZiBzY3JpcHQgaXMgc291cmNlZCBpbiBwc3Fs
LCByYXRoZXIgdGhhbiB2aWEgQ1JFQVRFIEVYVEVOU0lPTgorXGVjaG8gVXNlICJBTFRFUiBF
WFRFTlNJT04gYnRyZWVfZ2lzdCBVUERBVEUgVE8gJzEuOSciIHRvIGxvYWQgdGhpcyBmaWxl
LiBccXVpdAorCitBTFRFUiBPUEVSQVRPUiBGQU1JTFkgZ2lzdF9vaWRfb3BzIFVTSU5HIGdp
c3QgQURECisJRlVOQ1RJT04gMTEgKG9pZCwgb2lkKSBidG9pZHNvcnRzdXBwb3J0IChpbnRl
cm5hbCkgOworCitBTFRFUiBPUEVSQVRPUiBGQU1JTFkgZ2lzdF9pbnQyX29wcyBVU0lORyBn
aXN0IEFERAorCUZVTkNUSU9OIDExIChpbnQyLCBpbnQyKSBidGludDJzb3J0c3VwcG9ydCAo
aW50ZXJuYWwpIDsKKworQUxURVIgT1BFUkFUT1IgRkFNSUxZIGdpc3RfaW50NF9vcHMgVVNJ
TkcgZ2lzdCBBREQKKwlGVU5DVElPTiAxMSAoaW50NCwgaW50NCkgYnRpbnQ0c29ydHN1cHBv
cnQgKGludGVybmFsKSA7CisKK0FMVEVSIE9QRVJBVE9SIEZBTUlMWSBnaXN0X2ludDhfb3Bz
IFVTSU5HIGdpc3QgQURECisJRlVOQ1RJT04gMTEgKGludDgsIGludDgpIGJ0aW50OHNvcnRz
dXBwb3J0IChpbnRlcm5hbCkgOworCitBTFRFUiBPUEVSQVRPUiBGQU1JTFkgZ2lzdF9mbG9h
dDRfb3BzIFVTSU5HIGdpc3QgQURECisJRlVOQ1RJT04gMTEgKGZsb2F0NCwgZmxvYXQ0KSBi
dGZsb2F0NHNvcnRzdXBwb3J0IChpbnRlcm5hbCkgOworCitBTFRFUiBPUEVSQVRPUiBGQU1J
TFkgZ2lzdF9mbG9hdDhfb3BzIFVTSU5HIGdpc3QgQURECisJRlVOQ1RJT04gMTEgKGZsb2F0
OCwgZmxvYXQ4KSBidGZsb2F0OHNvcnRzdXBwb3J0IChpbnRlcm5hbCkgOworCitBTFRFUiBP
UEVSQVRPUiBGQU1JTFkgZ2lzdF90aW1lc3RhbXBfb3BzIFVTSU5HIGdpc3QgQURECisJRlVO
Q1RJT04gMTEgKHRpbWVzdGFtcCwgdGltZXN0YW1wKSB0aW1lc3RhbXBfc29ydHN1cHBvcnQg
KGludGVybmFsKSA7CisKK0FMVEVSIE9QRVJBVE9SIEZBTUlMWSBnaXN0X3RpbWVzdGFtcHR6
X29wcyBVU0lORyBnaXN0IEFERAorCUZVTkNUSU9OIDExICh0aW1lc3RhbXB0eiwgdGltZXN0
YW1wdHopIHRpbWVzdGFtcF9zb3J0c3VwcG9ydCAoaW50ZXJuYWwpIDsKKworLS0gQUxURVIg
T1BFUkFUT1IgRkFNSUxZIGdpc3RfdGltZV9vcHMgVVNJTkcgZ2lzdCBBREQKKy0tIAlGVU5D
VElPTiAxMiAodGltZSwgdGltZSkgZ2lzdF9zdHJhdG51bV9idHJlZSAoaW50MikgOworCitB
TFRFUiBPUEVSQVRPUiBGQU1JTFkgZ2lzdF9kYXRlX29wcyBVU0lORyBnaXN0IEFERAorCUZV
TkNUSU9OIDExIChkYXRlLCBkYXRlKSBkYXRlX3NvcnRzdXBwb3J0IChpbnRlcm5hbCkgOwor
CistLSBBTFRFUiBPUEVSQVRPUiBGQU1JTFkgZ2lzdF9pbnRlcnZhbF9vcHMgVVNJTkcgZ2lz
dCBBREQKKy0tIAlGVU5DVElPTiAxMiAoaW50ZXJ2YWwsIGludGVydmFsKSBnaXN0X3N0cmF0
bnVtX2J0cmVlIChpbnQyKSA7CisKKy0tIEFMVEVSIE9QRVJBVE9SIEZBTUlMWSBnaXN0X2Nh
c2hfb3BzIFVTSU5HIGdpc3QgQURECistLSAJRlVOQ1RJT04gMTIgKG1vbmV5LCBtb25leSkg
Z2lzdF9zdHJhdG51bV9idHJlZSAoaW50MikgOworCitBTFRFUiBPUEVSQVRPUiBGQU1JTFkg
Z2lzdF9tYWNhZGRyX29wcyBVU0lORyBnaXN0IEFERAorCUZVTkNUSU9OIDExIChtYWNhZGRy
LCBtYWNhZGRyKSBtYWNhZGRyX3NvcnRzdXBwb3J0IChpbnRlcm5hbCkgOworCitBTFRFUiBP
UEVSQVRPUiBGQU1JTFkgZ2lzdF90ZXh0X29wcyBVU0lORyBnaXN0IEFERAorCUZVTkNUSU9O
IDExICh0ZXh0LCB0ZXh0KSBidHRleHRzb3J0c3VwcG9ydCAoaW50ZXJuYWwpIDsKKworQUxU
RVIgT1BFUkFUT1IgRkFNSUxZIGdpc3RfYnBjaGFyX29wcyBVU0lORyBnaXN0IEFERAorCUZV
TkNUSU9OIDExIChicGNoYXIsIGJwY2hhcikgYnBjaGFyX3NvcnRzdXBwb3J0IChpbnRlcm5h
bCkgOworCitBTFRFUiBPUEVSQVRPUiBGQU1JTFkgZ2lzdF9ieXRlYV9vcHMgVVNJTkcgZ2lz
dCBBREQKKwlGVU5DVElPTiAxMSAoYnl0ZWEsIGJ5dGVhKSBieXRlYV9zb3J0c3VwcG9ydCAo
aW50ZXJuYWwpIDsKKworQUxURVIgT1BFUkFUT1IgRkFNSUxZIGdpc3RfbnVtZXJpY19vcHMg
VVNJTkcgZ2lzdCBBREQKKwlGVU5DVElPTiAxMSAobnVtZXJpYywgbnVtZXJpYykgbnVtZXJp
Y19zb3J0c3VwcG9ydCAoaW50ZXJuYWwpIDsKKworLS0gQUxURVIgT1BFUkFUT1IgRkFNSUxZ
IGdpc3RfYml0X29wcyBVU0lORyBnaXN0IEFERAorLS0JRlVOQ1RJT04gMTIgKGJpdCwgYml0
KSBnaXN0X3N0cmF0bnVtX2J0cmVlIChpbnQyKSA7CisKKy0tIEFMVEVSIE9QRVJBVE9SIEZB
TUlMWSBnaXN0X3ZiaXRfb3BzIFVTSU5HIGdpc3QgQURECistLQlGVU5DVElPTiAxMiAodmFy
Yml0LCB2YXJiaXQpIGdpc3Rfc3RyYXRudW1fYnRyZWUgKGludDIpIDsKKworQUxURVIgT1BF
UkFUT1IgRkFNSUxZIGdpc3RfaW5ldF9vcHMgVVNJTkcgZ2lzdCBBREQKKwlGVU5DVElPTiAx
MSAoaW5ldCwgaW5ldCkgbmV0d29ya19zb3J0c3VwcG9ydCAoaW50ZXJuYWwpIDsKKworQUxU
RVIgT1BFUkFUT1IgRkFNSUxZIGdpc3RfY2lkcl9vcHMgVVNJTkcgZ2lzdCBBREQKKwlGVU5D
VElPTiAxMSAoY2lkciwgY2lkcikgbmV0d29ya19zb3J0c3VwcG9ydCAoaW50ZXJuYWwpIDsK
KworLS1BTFRFUiBPUEVSQVRPUiBGQU1JTFkgZ2lzdF90aW1ldHpfb3BzIFVTSU5HIGdpc3Qg
QURECistLQlGVU5DVElPTiAxMiAodGltZXR6LCB0aW1ldHopIGdpc3Rfc3RyYXRudW1fYnRy
ZWUgKGludDIpIDsKKworQUxURVIgT1BFUkFUT1IgRkFNSUxZIGdpc3RfdXVpZF9vcHMgVVNJ
TkcgZ2lzdCBBREQKKwlGVU5DVElPTiAxMSAodXVpZCwgdXVpZCkgdXVpZF9zb3J0c3VwcG9y
dCAoaW50ZXJuYWwpIDsKKworLS0gQUxURVIgT1BFUkFUT1IgRkFNSUxZIGdpc3RfbWFjYWRk
cjhfb3BzIFVTSU5HIGdpc3QgQURECistLSAJRlVOQ1RJT04gMTIgKG1hY2FkZHI4LCBtYWNh
ZGRyOCkgZ2lzdF9zdHJhdG51bV9idHJlZSAoaW50MikgOworCistLSBBTFRFUiBPUEVSQVRP
UiBGQU1JTFkgZ2lzdF9lbnVtX29wcyBVU0lORyBnaXN0IEFERAorLS0gCUZVTkNUSU9OIDEy
IChhbnllbnVtLCBhbnllbnVtKSBnaXN0X3N0cmF0bnVtX2J0cmVlIChpbnQyKSA7CisKKy0t
IEFMVEVSIE9QRVJBVE9SIEZBTUlMWSBnaXN0X2Jvb2xfb3BzIFVTSU5HIGdpc3QgQURECist
LSAJRlVOQ1RJT04gMTIgKGJvb2wsIGJvb2wpIGdpc3Rfc3RyYXRudW1fYnRyZWUgKGludDIp
IDsKZGlmZiAtLWdpdCBhL2NvbnRyaWIvYnRyZWVfZ2lzdC9idHJlZV9naXN0LmNvbnRyb2wg
Yi9jb250cmliL2J0cmVlX2dpc3QvYnRyZWVfZ2lzdC5jb250cm9sCmluZGV4IGFiZjY2NTM4
ZjMyLi42OWQ5MzQxYTBhZCAxMDA2NDQKLS0tIGEvY29udHJpYi9idHJlZV9naXN0L2J0cmVl
X2dpc3QuY29udHJvbAorKysgYi9jb250cmliL2J0cmVlX2dpc3QvYnRyZWVfZ2lzdC5jb250
cm9sCkBAIC0xLDYgKzEsNiBAQAogIyBidHJlZV9naXN0IGV4dGVuc2lvbgogY29tbWVudCA9
ICdzdXBwb3J0IGZvciBpbmRleGluZyBjb21tb24gZGF0YXR5cGVzIGluIEdpU1QnCi1kZWZh
dWx0X3ZlcnNpb24gPSAnMS44JworZGVmYXVsdF92ZXJzaW9uID0gJzEuOScKIG1vZHVsZV9w
YXRobmFtZSA9ICckbGliZGlyL2J0cmVlX2dpc3QnCiByZWxvY2F0YWJsZSA9IHRydWUKIHRy
dXN0ZWQgPSB0cnVlCi0tIAoyLjQ0LjAKCg==

--------------urSzx6WEup1B0YpKxXwh0Rf6--