pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix right-anti-joins when the inner relation is proven unique 3+ messages / 1 participants [nested] [flat]
* pgsql: Fix right-anti-joins when the inner relation is proven unique @ 2024-07-08 01:32 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: Richard Guo @ 2024-07-08 01:32 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix right-anti-joins when the inner relation is proven unique For an inner_unique join, we always assume that the executor will stop scanning for matches after the first match. Therefore, for a mergejoin that is inner_unique and whose mergeclauses are sufficient to identify a match, we set the skip_mark_restore flag to true, indicating that the executor need not do mark/restore calls. However, merge-right-anti-join did not get this memo and continues scanning the inner side for matches after the first match. If there are duplicates in the outer scan, we may incorrectly skip matching some inner tuples, which can lead to wrong results. Here we fix this issue by ensuring that merge-right-anti-join also advances to next outer tuple after the first match in inner_unique cases. This also saves cycles by avoiding unnecessary scanning of inner tuples after the first match. Although hash-right-anti-join does not suffer from this wrong results issue, we apply the same change to it as well, to help save cycles for the same reason. Per bug #18522 from Antti Lampinen, and bug #18526 from Feliphe Pozzer. Back-patch to v16 where right-anti-join was introduced. Author: Richard Guo Discussion: https://postgr.es/m/18522-c7a8956126afdfd0@postgresql.org Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/507f2347e7f5257200e5630a0e0c9d0e33be2498 Modified Files -------------- src/backend/executor/nodeHashjoin.c | 21 +++++++------- src/backend/executor/nodeMergejoin.c | 21 +++++++------- src/test/regress/expected/join.out | 54 ++++++++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 24 ++++++++++++++++ 4 files changed, 100 insertions(+), 20 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Fix right-anti-joins when the inner relation is proven unique @ 2024-07-08 01:34 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: Richard Guo @ 2024-07-08 01:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix right-anti-joins when the inner relation is proven unique For an inner_unique join, we always assume that the executor will stop scanning for matches after the first match. Therefore, for a mergejoin that is inner_unique and whose mergeclauses are sufficient to identify a match, we set the skip_mark_restore flag to true, indicating that the executor need not do mark/restore calls. However, merge-right-anti-join did not get this memo and continues scanning the inner side for matches after the first match. If there are duplicates in the outer scan, we may incorrectly skip matching some inner tuples, which can lead to wrong results. Here we fix this issue by ensuring that merge-right-anti-join also advances to next outer tuple after the first match in inner_unique cases. This also saves cycles by avoiding unnecessary scanning of inner tuples after the first match. Although hash-right-anti-join does not suffer from this wrong results issue, we apply the same change to it as well, to help save cycles for the same reason. Per bug #18522 from Antti Lampinen, and bug #18526 from Feliphe Pozzer. Back-patch to v16 where right-anti-join was introduced. Author: Richard Guo Discussion: https://postgr.es/m/18522-c7a8956126afdfd0@postgresql.org Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/cccab85c2bea213ff03f47a9262d0d78275bd2af Modified Files -------------- src/backend/executor/nodeHashjoin.c | 21 +++++++------- src/backend/executor/nodeMergejoin.c | 21 +++++++------- src/test/regress/expected/join.out | 54 ++++++++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 24 ++++++++++++++++ 4 files changed, 100 insertions(+), 20 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Fix right-anti-joins when the inner relation is proven unique @ 2024-07-08 01:35 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 3+ messages in thread From: Richard Guo @ 2024-07-08 01:35 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix right-anti-joins when the inner relation is proven unique For an inner_unique join, we always assume that the executor will stop scanning for matches after the first match. Therefore, for a mergejoin that is inner_unique and whose mergeclauses are sufficient to identify a match, we set the skip_mark_restore flag to true, indicating that the executor need not do mark/restore calls. However, merge-right-anti-join did not get this memo and continues scanning the inner side for matches after the first match. If there are duplicates in the outer scan, we may incorrectly skip matching some inner tuples, which can lead to wrong results. Here we fix this issue by ensuring that merge-right-anti-join also advances to next outer tuple after the first match in inner_unique cases. This also saves cycles by avoiding unnecessary scanning of inner tuples after the first match. Although hash-right-anti-join does not suffer from this wrong results issue, we apply the same change to it as well, to help save cycles for the same reason. Per bug #18522 from Antti Lampinen, and bug #18526 from Feliphe Pozzer. Back-patch to v16 where right-anti-join was introduced. Author: Richard Guo Discussion: https://postgr.es/m/18522-c7a8956126afdfd0@postgresql.org Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/0ffc0acaf3bf301ba8fd43dc0e004b8b7c9ecd3a Modified Files -------------- src/backend/executor/nodeHashjoin.c | 21 +++++++------- src/backend/executor/nodeMergejoin.c | 21 +++++++------- src/test/regress/expected/join.out | 54 ++++++++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 24 ++++++++++++++++ 4 files changed, 100 insertions(+), 20 deletions(-) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-07-08 01:35 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-07-08 01:32 pgsql: Fix right-anti-joins when the inner relation is proven unique Richard Guo <rguo@postgresql.org> 2024-07-08 01:34 pgsql: Fix right-anti-joins when the inner relation is proven unique Richard Guo <rguo@postgresql.org> 2024-07-08 01:35 pgsql: Fix right-anti-joins when the inner relation is proven unique Richard Guo <rguo@postgresql.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox