Re: BUG #18261: Inconsistent results of SELECT affected by joined subqueries - Mailing list pgsql-bugs
From | Andrei Lepikhov |
---|---|
Subject | Re: BUG #18261: Inconsistent results of SELECT affected by joined subqueries |
Date | |
Msg-id | 2a7e3763-b8cc-40d1-9c6e-4ed2093edbbb@postgrespro.ru Whole thread Raw |
In response to | Re: BUG #18261: Inconsistent results of SELECT affected by joined subqueries (Richard Guo <guofenglinux@gmail.com>) |
List | pgsql-bugs |
On 2/1/2024 08:43, Richard Guo wrote: > > On Fri, Dec 29, 2023 at 12:32 AM Tom Lane <tgl@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.us>> wrote: > > Richard Guo <guofenglinux@gmail.com <mailto:guofenglinux@gmail.com>> > writes: > > I've looked into it a bit. The problem lies in how the SJE code > handles > > the transfer of qual clauses from the removed relation to the > remaining > > one. > > I am definitely starting to think that the SJE patch was not ready > for prime time. We keep finding not only minor but major problems > in it --- I'd call this one a "major" one. Is it time to revert > and rethink it from scratch? > > > I feel the same way. It seems to me that the SJE code still needs some > refactoring to get to a committable state. Being an author of the feature, I couldn't be all objective, of course. However, let me write some words on this code. About profitability. According to current database development trends, the feature looks helpful, where we see many auto-generated and syntactically redundant queries. So, IMO, some tools for simplifying the parse tree should be in the pocket of the DBMS. We do have numerous planner hooks that give way to well-known extensions like Citus or TimescaleDB, but tweaking the parse tree is gradually cheaper in many cases. By reducing the number of RangeTblEntries, clauses, and equivalence classes, we can profit significantly from this feature, especially with partitioned tables. Hence, this code is not only about self-join elimination. It also introduces machinery for redundant parse tree reduction not only for the case of dead tails, like reduce_outer_joins. It may be utilized by extensions in other ways, too. About weak points. We tried different ways to implement that feature during the development, even in the optimization stage. The current place and logic are the most effective. The self-join detection stage looks stable and polished enough. Only one part of the code is debatable - replacing one baserel (removing), involved in many relations with other DB objects, with another one (keeping). About development. Having it working in a PG fork and as a patch at the commitfest for a long time, we hadn't had so much food for thought as last three months when it was committed to the master. It raised new questions on weaknesses of setrefs, links from outer query blocks, integrity control of PlannerInfo, etc. Bugs we have been finding look at most not about architectural issues but about leaks in planner understanding, which is primarily correct about me personally. I think, having close scrutiny of the PlannerInfo fields and clause replacement strategy, we can achieve stable behaviour of this feature before the release and I already see progress in stability of the feature. As I see dynamics of discussions in the pgsql-bugs list, we already have a community team of people who detect SJE problems, develop the code and provide a review. Maybe it is also a positive outcome? Having written all the words above, I think we must formulate objective reasons if we want to revert this feature. Another way it can live on the mailing list for another five years without any progress. I, personally, vote for leaving this feature in the master. -- regards, Andrei Lepikhov Postgres Professional
pgsql-bugs by date: