[YSQL] Improve JOIN planning and execution #4519
Comments
m-iancu
changed the title
andrei-mart
added a commit
that referenced
this issue
Jul 6, 2021
Summary: Merge join was disabled for the lack of support of mark/restore in YB index scans, until such support is introduced. While technically possible, the feasibility of implementing that for a remote index scan is questionable. It means an RPC, to seek a key and start fetching from it, or even multiple RPCs, if scanning a secondary index. It would be more efficient to cache rows locally, and that is exactly what Materialize plan node does. To engage Materialize it is sufficient to tell optimizer that index scans do not support mark/restore. Our index scan may still support materialize-like functionality internally. It does pagination and normally keeps rows from last page in internall buffer, so it can back to marked position if it is still in buffer. So to fully support mark/restore we just need to append to the buffer, not to discard it if there is a mark. Test Plan: Add regression tests that would produce Merge Join. Make sure that inner Index Scan is covered by Materialize. To invoke relevant tests: ``` ybd --java-test 'org.yb.pgsql.TestPgRegressPlanner' ``` Reviewers: dsrinivasan, rskannan, mtakahara, mihnea Reviewed By: mihnea Subscribers: yql Differential Revision: https://phabricator.dev.yugabyte.com/D12133
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
As mentioned in #4496, currently the ybc access method (ybcin.c) does not support mark and restore position(
ammarkposandamrestrpos) for merge joins.We should implement those and than enable merge joins for the relevant use-cases.
Additionally for the supported algorithms/cases (e.g. nested loop, hash joins) we can do a better job of estimating the costs and rowsize for the join nodes.
The text was updated successfully, but these errors were encountered: