Hi, This is High 1 from my list in [1].
Since 2ebf25e7d70, query_planner() jumps back to its restart label after it removes a join and runs standard_qp_callback() again. Two things from the first pass are left behind. Both are on master, on REL_19_STABLE and on REL_18_STABLE (9f25197bf27), 18.6 does not have them. 1. adjust_group_pathkeys_for_groupagg() sets aggpresorted on the aggregates it picks and nothing clears it. When self join elimination removes a rel and the second pass picks a different presorted group, the stale flag makes the executor skip the sort. create table sj (id int primary key, p int, q int); insert into sj select i, i % 3, i from generate_series(1, 30) i; select count(distinct a.p), count(distinct a.q), sum(distinct a2.q) from sj a join sj a2 on a.id = a2.id; master returns 29 for the first column, the answer is 3. enable_self_join_elimination = off gives 3. 0001 clears the flag at the top of that function. 2. A removed rel keeps its tablesample. When the sample argument refers to another rel that the second pass then removes, ChangeVarNodes hits Assert(context->new_index != INVALID_VAR) in rewriteManip.c. Non assert builds write INVALID_VAR into a dead rte and seem fine. Richard looked at the rte fields in [2] and thought tablesample was safe, this is the case that is not. 0002 clears rte->tablesample next to securityQuals. 0003 adds both cases to join.sql. The patches apply to master, REL_19_STABLE and REL_18_STABLE. [1] https://postgr.es/m/cagrkxqsfdjqzp_kwrz7dv6lhx-csrkdnynnxgntrscenqtk...@mail.gmail.com [2] https://postgr.es/m/CAMbWs4-wxRAFx8RcsRBJ6mawf7mbOp9J-ms5n3Di0U8T=sx...@mail.gmail.com Thanks, Shihao
v1-0002-Clear-tablesample-of-a-removed-rel-in-join-remova.patch
Description: Binary data
v1-0003-Add-tests-for-planner-restart-after-join-removal.patch
Description: Binary data
v1-0001-Reset-aggpresorted-when-the-planner-restarts-afte.patch
Description: Binary data
