Content-Type: multipart/mixed; boundary="------------M82cO2MBpb30WAGLRuugYhAx"
Message-ID: <d1867416-198b-418a-be53-7df4e10aec62@gmail.com>
Date: Wed, 5 Mar 2025 09:41:42 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Andy Fan <zhihuifan1213@163.com>, Tomas Vondra <tomas@vondra.me>
From: Andrei Lepikhov <lepihov@gmail.com>
Subject: Hook for Selectivity Estimation in Query Planning
Archived-At: <https://www.postgresql.org/message-id/d1867416-198b-418a-be53-7df4e10aec62%40gmail.com>
Precedence: bulk

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

Hi,

I would like to discuss the introduction of a hook for evaluating the 
selectivity of an expression when searching for an optimal query plan. 
This topic has been brought up in various discussions, for example, in [1].

Currently, extensions that interact with the optimiser can only add 
their paths without the ability to influence the optimiser's decisions. 
As a result, when developing an extension that implements a new type of 
statistics (such as a histogram for composite types), utilises knowledge 
from previously executed queries, or implements some system of 
selectivity hints, we find ourselves writing a considerable amount of 
code. To ensure the reliable operation of the extension, this may end up 
in developing a separate optimiser or, at the very least, creating a 
custom join search (refer to core.c in the pg_hint_plan extension for an 
estimation of the amount of code required).

A hook for evaluating selectivity could streamline the development of 
methods to improve selectivity evaluation, making it easier to create 
new types of statistics and estimation methods (I would like to deal 
with join clauses estimation). Considering the limited amount of code 
involved and the upcoming code freeze, I propose adding such a hook to 
PostgreSQL 18 to assess how it simplifies extension development.

This proposed hook would complement the existing path hooks without 
overlapping in functionality. In my experience with implementing 
adaptive features in enterprise solutions, I believe that additional 
hooks could also be beneficial for estimating the number of groups and 
the amount of memory allocated, which is currently based solely on 
work_mem. However, these suggestions do not interfere with the current 
proposal and could be considered later.

Critique:
In general, a hook for evaluating the number of rows appears to be a 
more promising approach. It would allow the extension to access specific 
RelOptInfo data, thus providing insights into where the evaluation takes 
place within the plan. Consequently, this would enable a deeper 
influence on the query plan choice. However, implementing such a hook 
might be more invasive, requiring modifications to each cost function. 
Additionally, it addresses a slightly different issue and can be 
considered separately.

Attached is a patch containing the proposed hook code.

-- 
regards, Andrei Lepikhov

--------------M82cO2MBpb30WAGLRuugYhAx
Content-Type: text/plain; charset=UTF-8;
 name="0001-Introduce-selectivity-hook.patch"
Content-Disposition: attachment;
 filename="0001-Introduce-selectivity-hook.patch"
Content-Transfer-Encoding: base64

RnJvbSA3MTEzYTRkMGI0NWE0Y2YwMGI3ODA3NmQ1NTU3MGFkZTYwZmY5ODQxIE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiAiQW5kcmVpIFYuIExlcGlraG92IiA8bGVwaWhvdkBn
bWFpbC5jb20+CkRhdGU6IFdlZCwgNSBNYXIgMjAyNSAwOTozODozMCArMDEwMApTdWJqZWN0
OiBbUEFUQ0hdIEludHJvZHVjZSBzZWxlY3Rpdml0eSBob29rLgoKLS0tCiBzcmMvYmFja2Vu
ZC9vcHRpbWl6ZXIvcGF0aC9jbGF1c2VzZWwuYyB8ICA1ICsrKysrCiBzcmMvYmFja2VuZC91
dGlscy9hZHQvc2VsZnVuY3MuYyAgICAgICB8ICAxICsKIHNyYy9pbmNsdWRlL3V0aWxzL3Nl
bGZ1bmNzLmggICAgICAgICAgIHwgMTEgKysrKysrKysrKysKIDMgZmlsZXMgY2hhbmdlZCwg
MTcgaW5zZXJ0aW9ucygrKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9w
YXRoL2NsYXVzZXNlbC5jIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvY2xhdXNlc2Vs
LmMKaW5kZXggNWQ1MWY5N2YyMTkuLjZiNWQ0OWQwNzg2IDEwMDY0NAotLS0gYS9zcmMvYmFj
a2VuZC9vcHRpbWl6ZXIvcGF0aC9jbGF1c2VzZWwuYworKysgYi9zcmMvYmFja2VuZC9vcHRp
bWl6ZXIvcGF0aC9jbGF1c2VzZWwuYwpAQCAtMTI4LDYgKzEyOCwxMSBAQCBjbGF1c2VsaXN0
X3NlbGVjdGl2aXR5X2V4dChQbGFubmVySW5mbyAqcm9vdCwKIAlMaXN0Q2VsbCAgICpsOwog
CWludAkJCWxpc3RpZHg7CiAKKwlpZiAoY2xhdXNlbGlzdF9zZWxlY3Rpdml0eV9ob29rKQor
CQlyZXR1cm4gY2xhdXNlbGlzdF9zZWxlY3Rpdml0eV9ob29rKHJvb3QsIGNsYXVzZXMsIHZh
clJlbGlkLCBqb2ludHlwZSwKKwkJCQkJCQkJCQkgICBzamluZm8sICZlc3RpbWF0ZWRjbGF1
c2VzLAorCQkJCQkJCQkJCSAgIHVzZV9leHRlbmRlZF9zdGF0cyk7CisKIAkvKgogCSAqIElm
IHRoZXJlJ3MgZXhhY3RseSBvbmUgY2xhdXNlLCBqdXN0IGdvIGRpcmVjdGx5IHRvCiAJICog
Y2xhdXNlX3NlbGVjdGl2aXR5X2V4dCgpLiBOb25lIG9mIHdoYXQgd2UgbWlnaHQgZG8gYmVs
b3cgaXMgcmVsZXZhbnQuCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC91dGlscy9hZHQvc2Vs
ZnVuY3MuYyBiL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9zZWxmdW5jcy5jCmluZGV4IGMyOTE4
YzljODMxLi5lNjYzNDZlZjZiNCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0
L3NlbGZ1bmNzLmMKKysrIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3NlbGZ1bmNzLmMKQEAg
LTE0Nyw2ICsxNDcsNyBAQAogLyogSG9va3MgZm9yIHBsdWdpbnMgdG8gZ2V0IGNvbnRyb2wg
d2hlbiB3ZSBhc2sgZm9yIHN0YXRzICovCiBnZXRfcmVsYXRpb25fc3RhdHNfaG9va190eXBl
IGdldF9yZWxhdGlvbl9zdGF0c19ob29rID0gTlVMTDsKIGdldF9pbmRleF9zdGF0c19ob29r
X3R5cGUgZ2V0X2luZGV4X3N0YXRzX2hvb2sgPSBOVUxMOworY2xhdXNlbGlzdF9zZWxlY3Rp
dml0eV9ob29rX3R5cGUgY2xhdXNlbGlzdF9zZWxlY3Rpdml0eV9ob29rID0gTlVMTDsKIAog
c3RhdGljIGRvdWJsZSBlcXNlbF9pbnRlcm5hbChQR19GVU5DVElPTl9BUkdTLCBib29sIG5l
Z2F0ZSk7CiBzdGF0aWMgZG91YmxlIGVxam9pbnNlbF9pbm5lcihPaWQgb3BmdW5jb2lkLCBP
aWQgY29sbGF0aW9uLApkaWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUvdXRpbHMvc2VsZnVuY3Mu
aCBiL3NyYy9pbmNsdWRlL3V0aWxzL3NlbGZ1bmNzLmgKaW5kZXggZDM1OTM5NjUxZjMuLmNl
NGJhMjdkMTgzIDEwMDY0NAotLS0gYS9zcmMvaW5jbHVkZS91dGlscy9zZWxmdW5jcy5oCisr
KyBiL3NyYy9pbmNsdWRlL3V0aWxzL3NlbGZ1bmNzLmgKQEAgLTE0OSw2ICsxNDksMTcgQEAg
dHlwZWRlZiBib29sICgqZ2V0X2luZGV4X3N0YXRzX2hvb2tfdHlwZSkgKFBsYW5uZXJJbmZv
ICpyb290LAogCQkJCQkJCQkJCSAgIFZhcmlhYmxlU3RhdERhdGEgKnZhcmRhdGEpOwogZXh0
ZXJuIFBHRExMSU1QT1JUIGdldF9pbmRleF9zdGF0c19ob29rX3R5cGUgZ2V0X2luZGV4X3N0
YXRzX2hvb2s7CiAKKy8qIEhvb2tzIGZvciBwbHVnaW5zIHRvIGdldCBjb250cm9sIHdoZW4g
d2UgYXNrIGZvciBzZWxlY3Rpdml0eSBlc3RpbWF0aW9uICovCit0eXBlZGVmIFNlbGVjdGl2
aXR5ICgqY2xhdXNlbGlzdF9zZWxlY3Rpdml0eV9ob29rX3R5cGUpICgKKwkJCQkJCQkJCQkJ
CVBsYW5uZXJJbmZvICpyb290LAorCQkJCQkJCQkJCQkJTGlzdCAqY2xhdXNlcywKKwkJCQkJ
CQkJCQkJCWludCB2YXJSZWxpZCwKKwkJCQkJCQkJCQkJCUpvaW5UeXBlIGpvaW50eXBlLAor
CQkJCQkJCQkJCQkJU3BlY2lhbEpvaW5JbmZvICpzamluZm8sCisJCQkJCQkJCQkJCQlCaXRt
YXBzZXQgKiplc3RpbWF0ZWRjbGF1c2VzLAorCQkJCQkJCQkJCQkJYm9vbCB1c2VfZXh0ZW5k
ZWRfc3RhdHMpOworZXh0ZXJuIFBHRExMSU1QT1JUIGNsYXVzZWxpc3Rfc2VsZWN0aXZpdHlf
aG9va190eXBlIGNsYXVzZWxpc3Rfc2VsZWN0aXZpdHlfaG9vazsKKwogLyogRnVuY3Rpb25z
IGluIHNlbGZ1bmNzLmMgKi8KIAogZXh0ZXJuIHZvaWQgZXhhbWluZV92YXJpYWJsZShQbGFu
bmVySW5mbyAqcm9vdCwgTm9kZSAqbm9kZSwgaW50IHZhclJlbGlkLAotLSAKMi40OC4xCgo=


--------------M82cO2MBpb30WAGLRuugYhAx--