MIME-Version: 1.0
References: 
 <SEZPR06MB649422CDEBEBBA3915154EE58A232@SEZPR06MB6494.apcprd06.prod.outlook.com>
 <202aa06a-678c-430b-8f87-fb694b9703e8@gmail.com>
 <CAN-LCVNpN6zpWUMDXWePeb=_rUMB=k2zwzN_f3K-1mKHUZVOxg@mail.gmail.com>
In-Reply-To: 
 <CAN-LCVNpN6zpWUMDXWePeb=_rUMB=k2zwzN_f3K-1mKHUZVOxg@mail.gmail.com>
From: Nikita Malakhov <hukutoc@gmail.com>
Date: Fri, 29 Nov 2024 17:27:18 +0300
Message-ID: 
 <CAN-LCVPco=XwNyj2eCM3KAmnR9sSqbpyS0dyKAiDuzQ3C1SFxg@mail.gmail.com>
Subject: Re: Performance of Query 60 on TPC-DS Benchmark
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: Ba Jinsheng <bajinsheng@u.nus.edu>,
	"pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000d0de9e06280dff2b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAN-LCVPco%3DXwNyj2eCM3KAmnR9sSqbpyS0dyKAiDuzQ3C1SFxg%40mail.gmail.com>
Precedence: bulk

--000000000000d0de9e06280dff2b
Content-Type: multipart/alternative; boundary="000000000000d0de9d06280dff29"

--000000000000d0de9d06280dff29
Content-Type: text/plain; charset="UTF-8"

Hi!

Please check the following proposal (patch in attach).
The main idea is to reject only obviously worse paths (costs considerably
more
than compared one), and to pass pre-calculated startup cost to precheck
function
for more accurate comparison.

>
> --
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/

--000000000000d0de9d06280dff29
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi!<div><br></div><div>Please check the f=
ollowing proposal (patch in attach).</div><div>The main idea is to reject o=
nly=C2=A0obviously worse paths (costs considerably more</div><div>than comp=
ared one), and to pass pre-calculated startup cost to precheck function</di=
v><div>for more accurate comparison.</div></div><div class=3D"gmail_quote g=
mail_quote_container"><blockquote class=3D"gmail_quote" style=3D"margin:0px=
 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><di=
v><br></div></blockquote></div><span class=3D"gmail_signature_prefix">-- </=
span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div s=
tyle=3D"color:rgb(34,34,34)">Regards,</div><div style=3D"color:rgb(34,34,34=
)">Nikita Malakhov</div><div style=3D"color:rgb(34,34,34)">Postgres Profess=
ional</div><div style=3D"color:rgb(34,34,34)">The Russian Postgres Company<=
/div><div style=3D"color:rgb(34,34,34)"><a href=3D"https://postgrespro.ru/"=
 target=3D"_blank">https://postgrespro.ru/</a></div></div></div></div>

--000000000000d0de9d06280dff29--

--000000000000d0de9e06280dff2b
Content-Type: application/octet-stream; name="v1-0001-ppath-precheck.patch"
Content-Disposition: attachment; filename="v1-0001-ppath-precheck.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m42u81mh0>
X-Attachment-Id: f_m42u81mh0

RnJvbSBlMWU1ODlmNWMzMzBhNjhkYmUzZThjY2QxMTUxMGU5N2JkOWIxZDAxIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBOaWtpdGEgTWFsYWtob3YgPG4ubWFsYWtob3ZAcG9zdGdyZXNw
cm8ucnU+CkRhdGU6IEZyaSwgMjkgTm92IDIwMjQgMTA6Mjc6MDIgKzAzMDAKU3ViamVjdDogW1BB
VENIXSBDb25zaWRlcmluZyBmcmFjdGlvbmFsIGhhc2hqb2luIHBhdGguCgpUUEMtRFMgYmVuY2ht
YXJrIHF1ZXJ5IDYwIHNob3dzIHVzYWdlIG9mIHZlcnkgaW5lZmZlY3RpdmUgcGxhbiBpbnN0ZWFk
Cm9mIG9wdGltYWwgYmVjYXVzZSBvZiBwYXJ0aWFsIHBsYW5zIGFyZSByZWplY3RlZCBieSBhZGRf
cGFydGlhbF9wYXRoX3ByZWNoZWNrLApkaXNjdXNzaW9uIFsxXS4gTW9kaWZ5aW5nIGFkZF9wYXJ0
aWFsX3BhdGhfcHJlY2hlY2sgdG8gcmVqZWN0IHBhdGggb25seQppZiBpdCBpcyBjbGVhcmx5IGxl
c3MgZWZmZWN0aXZlIChjb3N0IGlzIGNvbnNpZGVyYWJseSBiaWdnZXIpLCBhbmQgcGFzc2luZwpw
YXJ0aWFsIHN0YXJ0dXAgY29zdCBpbnN0ZWFkIG9mIHVzaW5nIHRvdGFsIGZvciBib3RoIHN0YXJ0
dXAgYW5kIHRvdGFsCmFsbG93cyB0byB1c2UgbW9yZSBlZmZlY3RpdmUgcGFydGlhbCBwYXRocy4K
CkF1dGhvcjogTmlraXRhIE1hbGFraG92IDxuLm1hbGFraG92QHBvc3RncmVzcHJvLnJ1PgoKWzFd
IGh0dHBzOi8vd3d3LnBvc3RncmVzcWwub3JnL21lc3NhZ2UtaWQvZmxhdC9TRVpQUjA2TUI2NDk0
MjJDREVCRUJCQTM5MTUxNTRFRTU4QTIzMiU0MFNFWlBSMDZNQjY0OTQuYXBjcHJkMDYucHJvZC5v
dXRsb29rLmNvbQotLS0KIGNvbnRyaWIvaXNfanNvbmJfdmFsaWQgICAgICAgICAgICAgICAgfCAg
MSArCiBzcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9qb2lucGF0aC5jIHwgIDYgKysrLS0tCiBz
cmMvYmFja2VuZC9vcHRpbWl6ZXIvdXRpbC9wYXRobm9kZS5jIHwgMTEgKysrKy0tLS0tLS0KIHNy
Yy9pbmNsdWRlL29wdGltaXplci9wYXRobm9kZS5oICAgICAgfCAgMiArLQogNCBmaWxlcyBjaGFu
Z2VkLCA5IGluc2VydGlvbnMoKyksIDExIGRlbGV0aW9ucygtKQogY3JlYXRlIG1vZGUgMTYwMDAw
IGNvbnRyaWIvaXNfanNvbmJfdmFsaWQKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9vcHRpbWl6
ZXIvcGF0aC9qb2lucGF0aC5jIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvam9pbnBhdGgu
YwppbmRleCAzOTcxMTM4NDgwLi4xYzcyMzhmOGQyIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9v
cHRpbWl6ZXIvcGF0aC9qb2lucGF0aC5jCisrKyBiL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRo
L2pvaW5wYXRoLmMKQEAgLTk5OSw3ICs5OTksNyBAQCB0cnlfcGFydGlhbF9uZXN0bG9vcF9wYXRo
KFBsYW5uZXJJbmZvICpyb290LAogCSAqLwogCWluaXRpYWxfY29zdF9uZXN0bG9vcChyb290LCAm
d29ya3NwYWNlLCBqb2ludHlwZSwKIAkJCQkJCSAgb3V0ZXJfcGF0aCwgaW5uZXJfcGF0aCwgZXh0
cmEpOwotCWlmICghYWRkX3BhcnRpYWxfcGF0aF9wcmVjaGVjayhqb2lucmVsLCB3b3Jrc3BhY2Uu
ZGlzYWJsZWRfbm9kZXMsCisJaWYgKCFhZGRfcGFydGlhbF9wYXRoX3ByZWNoZWNrKGpvaW5yZWws
IHdvcmtzcGFjZS5kaXNhYmxlZF9ub2Rlcywgd29ya3NwYWNlLnN0YXJ0dXBfY29zdCwKIAkJCQkJ
CQkJICAgd29ya3NwYWNlLnRvdGFsX2Nvc3QsIHBhdGhrZXlzKSkKIAkJcmV0dXJuOwogCkBAIC0x
MTY5LDcgKzExNjksNyBAQCB0cnlfcGFydGlhbF9tZXJnZWpvaW5fcGF0aChQbGFubmVySW5mbyAq
cm9vdCwKIAkJCQkJCSAgIG91dGVyc29ydGtleXMsIGlubmVyc29ydGtleXMsCiAJCQkJCQkgICBl
eHRyYSk7CiAKLQlpZiAoIWFkZF9wYXJ0aWFsX3BhdGhfcHJlY2hlY2soam9pbnJlbCwgd29ya3Nw
YWNlLmRpc2FibGVkX25vZGVzLAorCWlmICghYWRkX3BhcnRpYWxfcGF0aF9wcmVjaGVjayhqb2lu
cmVsLCB3b3Jrc3BhY2UuZGlzYWJsZWRfbm9kZXMsIHdvcmtzcGFjZS5zdGFydHVwX2Nvc3QsCiAJ
CQkJCQkJCSAgIHdvcmtzcGFjZS50b3RhbF9jb3N0LCBwYXRoa2V5cykpCiAJCXJldHVybjsKIApA
QCAtMTMwMCw3ICsxMzAwLDcgQEAgdHJ5X3BhcnRpYWxfaGFzaGpvaW5fcGF0aChQbGFubmVySW5m
byAqcm9vdCwKIAkgKi8KIAlpbml0aWFsX2Nvc3RfaGFzaGpvaW4ocm9vdCwgJndvcmtzcGFjZSwg
am9pbnR5cGUsIGhhc2hjbGF1c2VzLAogCQkJCQkJICBvdXRlcl9wYXRoLCBpbm5lcl9wYXRoLCBl
eHRyYSwgcGFyYWxsZWxfaGFzaCk7Ci0JaWYgKCFhZGRfcGFydGlhbF9wYXRoX3ByZWNoZWNrKGpv
aW5yZWwsIHdvcmtzcGFjZS5kaXNhYmxlZF9ub2RlcywKKwlpZiAoIWFkZF9wYXJ0aWFsX3BhdGhf
cHJlY2hlY2soam9pbnJlbCwgd29ya3NwYWNlLmRpc2FibGVkX25vZGVzLCB3b3Jrc3BhY2Uuc3Rh
cnR1cF9jb3N0LAogCQkJCQkJCQkgICB3b3Jrc3BhY2UudG90YWxfY29zdCwgTklMKSkKIAkJcmV0
dXJuOwogCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvdXRpbC9wYXRobm9kZS5j
IGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3V0aWwvcGF0aG5vZGUuYwppbmRleCBmYzk3YmY2ZWUy
Li4yYTEwODhlNGJjIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvdXRpbC9wYXRo
bm9kZS5jCisrKyBiL3NyYy9iYWNrZW5kL29wdGltaXplci91dGlsL3BhdGhub2RlLmMKQEAgLTkx
OCw3ICs5MTgsNyBAQCBhZGRfcGFydGlhbF9wYXRoKFJlbE9wdEluZm8gKnBhcmVudF9yZWwsIFBh
dGggKm5ld19wYXRoKQogICogaXMgc3VyZWx5IGEgbG9zZXIuCiAgKi8KIGJvb2wKLWFkZF9wYXJ0
aWFsX3BhdGhfcHJlY2hlY2soUmVsT3B0SW5mbyAqcGFyZW50X3JlbCwgaW50IGRpc2FibGVkX25v
ZGVzLAorYWRkX3BhcnRpYWxfcGF0aF9wcmVjaGVjayhSZWxPcHRJbmZvICpwYXJlbnRfcmVsLCBp
bnQgZGlzYWJsZWRfbm9kZXMsIENvc3Qgc3RhcnR1cF9jb3N0LAogCQkJCQkJICBDb3N0IHRvdGFs
X2Nvc3QsIExpc3QgKnBhdGhrZXlzKQogewogCUxpc3RDZWxsICAgKnAxOwpAQCAtOTM3LDE2ICs5
MzcsMTMgQEAgYWRkX3BhcnRpYWxfcGF0aF9wcmVjaGVjayhSZWxPcHRJbmZvICpwYXJlbnRfcmVs
LCBpbnQgZGlzYWJsZWRfbm9kZXMsCiAJZm9yZWFjaChwMSwgcGFyZW50X3JlbC0+cGFydGlhbF9w
YXRobGlzdCkKIAl7CiAJCVBhdGgJICAgKm9sZF9wYXRoID0gKFBhdGggKikgbGZpcnN0KHAxKTsK
LQkJUGF0aEtleXNDb21wYXJpc29uIGtleXNjbXA7CiAKIAkJa2V5c2NtcCA9IGNvbXBhcmVfcGF0
aGtleXMocGF0aGtleXMsIG9sZF9wYXRoLT5wYXRoa2V5cyk7CiAJCWlmIChrZXlzY21wICE9IFBB
VEhLRVlTX0RJRkZFUkVOVCkKIAkJewotCQkJaWYgKHRvdGFsX2Nvc3QgPiBvbGRfcGF0aC0+dG90
YWxfY29zdCAqIFNURF9GVVpaX0ZBQ1RPUiAmJgotCQkJCWtleXNjbXAgIT0gUEFUSEtFWVNfQkVU
VEVSMSkKKwkJCWlmICh0b3RhbF9jb3N0ID4gb2xkX3BhdGgtPnRvdGFsX2Nvc3QgKiBTVERfRlVa
Wl9GQUNUT1IpCiAJCQkJcmV0dXJuIGZhbHNlOwotCQkJaWYgKG9sZF9wYXRoLT50b3RhbF9jb3N0
ID4gdG90YWxfY29zdCAqIFNURF9GVVpaX0ZBQ1RPUiAmJgotCQkJCWtleXNjbXAgIT0gUEFUSEtF
WVNfQkVUVEVSMikKKwkJCWlmIChvbGRfcGF0aC0+dG90YWxfY29zdCA+IHRvdGFsX2Nvc3QgKiBT
VERfRlVaWl9GQUNUT1IpCiAJCQkJcmV0dXJuIHRydWU7CiAJCX0KIAl9CkBAIC05NjIsNyArOTU5
LDcgQEAgYWRkX3BhcnRpYWxfcGF0aF9wcmVjaGVjayhSZWxPcHRJbmZvICpwYXJlbnRfcmVsLCBp
bnQgZGlzYWJsZWRfbm9kZXMsCiAJICogcGFydGlhbCBwYXRoOyB0aGUgcmVzdWx0aW5nIHBsYW5z
LCBpZiBydW4gaW4gcGFyYWxsZWwsIHdpbGwgYmUgcnVuIHRvCiAJICogY29tcGxldGlvbi4KIAkg
Ki8KLQlpZiAoIWFkZF9wYXRoX3ByZWNoZWNrKHBhcmVudF9yZWwsIGRpc2FibGVkX25vZGVzLCB0
b3RhbF9jb3N0LCB0b3RhbF9jb3N0LAorCWlmICghYWRkX3BhdGhfcHJlY2hlY2socGFyZW50X3Jl
bCwgZGlzYWJsZWRfbm9kZXMsIHN0YXJ0dXBfY29zdCwgdG90YWxfY29zdCwKIAkJCQkJCSAgIHBh
dGhrZXlzLCBOVUxMKSkKIAkJcmV0dXJuIGZhbHNlOwogCmRpZmYgLS1naXQgYS9zcmMvaW5jbHVk
ZS9vcHRpbWl6ZXIvcGF0aG5vZGUuaCBiL3NyYy9pbmNsdWRlL29wdGltaXplci9wYXRobm9kZS5o
CmluZGV4IDEwMzVlNjU2MGMuLmU2MDgxZGUzNzAgMTAwNjQ0Ci0tLSBhL3NyYy9pbmNsdWRlL29w
dGltaXplci9wYXRobm9kZS5oCisrKyBiL3NyYy9pbmNsdWRlL29wdGltaXplci9wYXRobm9kZS5o
CkBAIC0zMiw3ICszMiw3IEBAIGV4dGVybiBib29sIGFkZF9wYXRoX3ByZWNoZWNrKFJlbE9wdElu
Zm8gKnBhcmVudF9yZWwsIGludCBkaXNhYmxlZF9ub2RlcywKIAkJCQkJCQkgIExpc3QgKnBhdGhr
ZXlzLCBSZWxpZHMgcmVxdWlyZWRfb3V0ZXIpOwogZXh0ZXJuIHZvaWQgYWRkX3BhcnRpYWxfcGF0
aChSZWxPcHRJbmZvICpwYXJlbnRfcmVsLCBQYXRoICpuZXdfcGF0aCk7CiBleHRlcm4gYm9vbCBh
ZGRfcGFydGlhbF9wYXRoX3ByZWNoZWNrKFJlbE9wdEluZm8gKnBhcmVudF9yZWwsCi0JCQkJCQkJ
CQkgIGludCBkaXNhYmxlZF9ub2RlcywKKwkJCQkJCQkJCSAgaW50IGRpc2FibGVkX25vZGVzLCBD
b3N0IHN0YXJ0dXBfY29zdCwKIAkJCQkJCQkJCSAgQ29zdCB0b3RhbF9jb3N0LCBMaXN0ICpwYXRo
a2V5cyk7CiAKIGV4dGVybiBQYXRoICpjcmVhdGVfc2Vxc2Nhbl9wYXRoKFBsYW5uZXJJbmZvICpy
b290LCBSZWxPcHRJbmZvICpyZWwsCi0tIAoyLjI1LjEKCg==
--000000000000d0de9e06280dff2b--