[YSQL] Add Support for PostgreSQL parallelism for YbSeqScan plan node #18095
Assignees
Labels
area/ysql
Yugabyte SQL (YSQL)
kind/enhancement
This is an enhancement of an existing feature
priority/medium
Medium priority issue
Comments
sushantrmishra
added
area/ysql
yugabyte-ci
added
kind/bug
10 tasks
andrei-mart
added a commit
that referenced
this issue
Nov 29, 2023
Summary: Enable Postgres' parallel query feature and implement parallel scan of YB tables in YBSeqScan, IndexScan, IndexOnlyScan nodes. Feature is enabled in preview mode, that is, it is disabled by default, to enable: ``` set yb_parallel_range_rows to 10000; ``` indicates the number of estimated rows per parallel worker. Smaller table scans are not parallelized, default 0 effectively disables the feature. The parameter defines minimum number of parallel workers, while `max_parallel_workers_per_gather` works as the maximum. ``` set yb_enable_base_scans_cost_model to true; ``` since parallel query cost improvements are factored in Yugabyte costing functions. Also make sure the target tables are large and ANALYZE was done for them. Due to planned parallelization overhead optimizer selects parallel plan only if it thinks the target table is large, default 1000 rows would not be sufficient. The feature depends on the DocDB ability to return key ranges for parallel scan implemented in D26978. Those key ranges are stored in the shared memory buffer from where they are taken one at a time by the parallel workers. Transaction consistency between parallel workers is ensured by main backend sharing its session and transaction context. Jira: DB-7135 Test Plan: ybd --java-test org.yb.pgsql.TestPgRegressParallel ybd --cxx-test pggate_test_select --gtest_filter PggateTestSelect.TestSelectHashRanges ybd --cxx-test pggate_test_select --gtest_filter PggateTestSelect.TestSelectScanRanges Reviewers: sergei, timur, jason, pjain, tnayak Reviewed By: pjain, tnayak Subscribers: ybase, smishra, yql, bogdan Differential Revision: https://phorge.dev.yugabyte.com/D28398
jasonyb
pushed a commit
that referenced
this issue
Mar 14, 2024
Summary: Enable Postgres' parallel query feature and implement parallel scan of YB tables in YBSeqScan, IndexScan, IndexOnlyScan nodes. Feature is enabled in preview mode, that is, it is disabled by default, to enable: ``` set yb_parallel_range_rows to 10000; ``` indicates the number of estimated rows per parallel worker. Smaller table scans are not parallelized, default 0 effectively disables the feature. The parameter defines minimum number of parallel workers, while `max_parallel_workers_per_gather` works as the maximum. ``` set yb_enable_base_scans_cost_model to true; ``` since parallel query cost improvements are factored in Yugabyte costing functions. Also make sure the target tables are large and ANALYZE was done for them. Due to planned parallelization overhead optimizer selects parallel plan only if it thinks the target table is large, default 1000 rows would not be sufficient. The feature depends on the DocDB ability to return key ranges for parallel scan implemented in D26978. Those key ranges are stored in the shared memory buffer from where they are taken one at a time by the parallel workers. Transaction consistency between parallel workers is ensured by main backend sharing its session and transaction context. Jira: DB-7135 Test Plan: ybd --java-test org.yb.pgsql.TestPgRegressParallel ybd --cxx-test pggate_test_select --gtest_filter PggateTestSelect.TestSelectHashRanges ybd --cxx-test pggate_test_select --gtest_filter PggateTestSelect.TestSelectScanRanges Reviewers: sergei, timur, jason, pjain, tnayak Reviewed By: pjain, tnayak Subscribers: ybase, smishra, yql, bogdan Differential Revision: https://phorge.dev.yugabyte.com/D28398
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Jira Link: DB-7135
Description
Add Support for PostgreSQL parallelism for YbSeqScan plan node.
Warning: Please confirm that this issue does not contain any sensitive information
The text was updated successfully, but these errors were encountered: