Re: Making Vars outer-join aware

Hans Buschmann <buschmann@nidsa.net>

From: Hans Buschmann <buschmann@nidsa.net>

To: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, Tom Lane <tgl@sss.pgh.pa.us>

Date: 2023-01-24T10:11:00Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Re-allow INDEX_VAR as rt_index in ChangeVarNodes().
- fbf80421ead5 16.0 landed
Fix thinkos in have_unsafe_outer_join_ref; reduce to Assert check.
- f50f029c497d 16.0 landed
Invent "join domains" to replace the below_outer_join hack.
- 3bef56e11650 16.0 landed
Do assorted mop-up in the planner.
- b448f1c8d83f 16.0 landed
Make Vars be outer-join-aware.
- 2489d76c4906 16.0 landed
Invent "multibitmapsets", and use them to speed up antijoin detection.
- e9e26b5e7166 16.0 landed
Add basic regression tests for semi/antijoin recognition.
- 0043aa6b8597 16.0 landed
Improve performance of adjust_appendrel_attrs_multilevel.
- 2f17b57017e5 16.0 landed
Refactor addition of PlaceHolderVars to joinrel targetlists.
- afa0ec30bfd1 16.0 landed
Use an explicit state flag to control PlaceHolderInfo creation.
- b3ff6c742f6c 16.0 landed
Make PlaceHolderInfo lookup O(1).
- 6569ca43973b 16.0 landed

Hello Tom


I just noticed your new efforts in this area.


I wanted to recurr to my old thread [1] considering constant propagation of quals.


You gave an elaborated explanation at that time, but my knowledge was/is not yet sufficient to reveil the technical details.


In our application the described method is widespread used with much success (now at pg15.1 Fedora), but for unexperienced SQL authors this is not really obviously to choose (i.e. using the explicit constant xx_season=3 as qual). This always requires a "Macro" processor to compose the queries (in my case php) and a lot of programmer effort in the source code.


I can't review/understand your patchset for the planner, but since it covers the same area, the beformentioned optimization could perhaps be addressed too.


With respect of the nullability of these quals I immediately changed all of them to NOT NULL, which seems the most natural way when these quals are also used for partioning.


[1]  https://www.postgresql.org/message-id/1571413123735.26467@nidsa.net


Thanks for looking


Hans Buschmann