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.

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

Optimize cross-shard routing for UPDATE/DELETE queries with IN clauses #6343

Closed
aquarapid opened this issue Jun 18, 2020 · 0 comments · Fixed by #15455
Closed

Optimize cross-shard routing for UPDATE/DELETE queries with IN clauses #6343

aquarapid opened this issue Jun 18, 2020 · 0 comments · Fixed by #15455
Labels
Component: Query Serving Type: Enhancement Logical improvement (somewhere between a bug and feature)

Comments

@aquarapid
Copy link
Contributor

In #6116 we optimized shard targeting for IN clause statements providing a vindex key. However, unlike for SELECT statement, we do not optimize the actual content of the IN clause during the scatter.

An example:

SELECT optimizes the IN clause as well as optimizes the targeting:

+ vtexplain -schema-file schema.sql -vschema-file vschema.json -shards 8 -sql 'select * from bar where c1 in (1,2,3,4)'
----------------------------------------------------------------------
select * from bar where c1 in (1,2,3,4)

1 ks1/-20: select * from bar where c1 in (1, 2) limit 10001
1 ks1/40-60: select * from bar where c1 in (3) limit 10001
1 ks1/c0-e0: select * from bar where c1 in (4) limit 10001

----------------------------------------------------------------------

UPDATE just optimizes the targeting:

+ vtexplain -schema-file schema.sql -vschema-file vschema.json -shards 8 -sql 'update bar set c2=1 where c1 in (1,2,3,4)'
----------------------------------------------------------------------
update bar set c2=1 where c1 in (1,2,3,4)

1 ks1/-20: begin
1 ks1/-20: update bar set c2 = 1 where c1 in (1, 2, 3, 4) limit 10001
1 ks1/40-60: begin
1 ks1/40-60: update bar set c2 = 1 where c1 in (1, 2, 3, 4) limit 10001
1 ks1/c0-e0: begin
1 ks1/c0-e0: update bar set c2 = 1 where c1 in (1, 2, 3, 4) limit 10001
2 ks1/40-60: commit
3 ks1/-20: commit
4 ks1/c0-e0: commit

Obviously this isn't a functionality issue; but it does avoid unnecessary key lookups on each shard backend instance.

@aquarapid aquarapid added the Type: Enhancement Logical improvement (somewhere between a bug and feature) label Jun 18, 2020
@hallaroo hallaroo added the P2 label Jan 21, 2021
@hallaroo hallaroo changed the title Optimize IN clauses for DML (UPDATE/DELETE) Optimize cross-shard routing for UPDATE/DELETE queries with IN clauses Jan 21, 2021
@ajm188 ajm188 removed the P2 label Mar 9, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Component: Query Serving Type: Enhancement Logical improvement (somewhere between a bug and feature)
Projects
Development

Successfully merging a pull request may close this issue.

4 participants