Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[YSQL] Use geo-location (tablespace) as a cost component in query planner #7535

Closed
m-iancu opened this issue Mar 8, 2021 · 0 comments
Closed

[YSQL] Use geo-location (tablespace) as a cost component in query planner #7535

m-iancu opened this issue Mar 8, 2021 · 0 comments
Assignees
@tanujnay112
Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue
Milestone
2.7.x

Comments

@m-iancu
Copy link
Contributor

m-iancu commented Mar 8, 2021 •
edited by yugabyte-ci
Loading

Jira Link: DB-1318
Specifically, if multiple indexes exist in different tablespaces but are otherwise identical (i.e. w.r.t to included and indexed columns) we should pick the one that (best) matches the placement info of the current node (if any).
We'd need to:

  1. get the tablespace info from the metadata (and their respective placements) -- pg_tablespace table (should be in cache, if not we might need to add it).
  2. Get the placement info of the current node (we should have this in the tserver FLAGS_placement_cloud/region/zone flags) -- see YBUnsupportedFeatureSignalLevel in pg_yb_common.c for how to use them in YSQL layer.
  3. In the cost calculation from ybcam.c we should use this data appropriately.

cc @deeps1991 @ramsrivatsa
@m-iancu m-iancu added the area/ysql Yugabyte SQL (YSQL) label Mar 8, 2021
@m-iancu m-iancu added this to the 2.7.x milestone Apr 21, 2021
@deeps1991 deeps1991 mentioned this issue May 11, 2021
Closed
tanujnay112 pushed a commit that referenced this issue May 25, 2021
[#7535] YSQL: Use geo-location (tablespace) to cost index scans
bebd181 
Summary:
This introduces functionality to select an index placement to scan from based on their placements encoded in a tablespace.

We cost a tablespace by first costing each of its placement options and pessimistically choosing the worst of the placement options. Each placement option is scored based on a prefix comparison with our current node placement and assigns costs appropriately. Here are the current cost assignments:

The placement is zone-local (cloud, region and zone match): 9.4
The placement is region-local (cloud and region match): 9.5
The placement is cloud-local (cloud matches): 10.0
The placement is not-local (nothing matches): 10.0

The main motivation behind these costs is to keep the not-local scenario more expensive than the default cost that one obtains without considering tablespaces (10.0).

Test Plan: ybd --scb --sj org.yb.pgsql.TestPgRegressTablespaces#testPgRegressTablespaces

Reviewers: rskannan, dmitry, dsrinivasan, mihnea

Reviewed By: mihnea

Subscribers: jason, zyu, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D10982
YintongMa pushed a commit to YintongMa/yugabyte-db that referenced this issue May 26, 2021
@YintongMa
[yugabyte#7535] YSQL: Use geo-location (tablespace) to cost index scans
d98193f 
Summary:
This introduces functionality to select an index placement to scan from based on their placements encoded in a tablespace.

We cost a tablespace by first costing each of its placement options and pessimistically choosing the worst of the placement options. Each placement option is scored based on a prefix comparison with our current node placement and assigns costs appropriately. Here are the current cost assignments:

The placement is zone-local (cloud, region and zone match): 9.4
The placement is region-local (cloud and region match): 9.5
The placement is cloud-local (cloud matches): 10.0
The placement is not-local (nothing matches): 10.0

The main motivation behind these costs is to keep the not-local scenario more expensive than the default cost that one obtains without considering tablespaces (10.0).

Test Plan: ybd --scb --sj org.yb.pgsql.TestPgRegressTablespaces#testPgRegressTablespaces

Reviewers: rskannan, dmitry, dsrinivasan, mihnea

Reviewed By: mihnea

Subscribers: jason, zyu, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D10982
@m-iancu m-iancu added this to YQL-beta Dec 7, 2021
@sushantrmishra sushantrmishra mentioned this issue Mar 23, 2022
Open
@yugabyte-ci yugabyte-ci added kind/bug This issue is a bug priority/medium Medium priority issue labels Jun 8, 2022
@yugabyte-ci yugabyte-ci added kind/enhancement This is an enhancement of an existing feature and removed kind/bug This issue is a bug labels Oct 5, 2022
@tverona1 tverona1 mentioned this issue Feb 6, 2023
Closed
@tverona1 tverona1 closed this as completed Feb 7, 2023
@github-project-automation github-project-automation bot moved this to Done in YQL-beta Feb 7, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@tanujnay112 tanujnay112

Labels
area/ysql Yugabyte SQL (YSQL) kind/enhancement This is an enhancement of an existing feature priority/medium Medium priority issue
Projects
YQL-beta
Status: Done
Milestone
2.7.x
Development

No branches or pull requests
6 participants
@tanujnay112 @m-iancu @ramsrivatsa @tverona1 @yugabyte-ci @deeps1991