[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
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

Unify optimize calls between top-level select and sub-select #15728

Draft
wants to merge 1 commit into
base: master
Choose a base branch
from
Draft

Unify optimize calls between top-level select and sub-select #15728

wants to merge 1 commit into from
+62 −70

Conversation

mfussenegger
Copy link
Member
@mfussenegger mfussenegger commented Mar 20, 2024 •
edited
Loading

Side effect of the change is that this now enables fetch optimization
for sub-queries.

Given the impact of this, I'd hold this off for 5.8

@mfussenegger mfussenegger marked this pull request as draft March 20, 2024 11:18
@mfussenegger mfussenegger marked this pull request as ready for review April 8, 2024 06:59
@mfussenegger mfussenegger requested a review from matriv April 8, 2024 06:59
matriv
matriv reviewed Apr 8, 2024
View reviewed changes
Copy link
Contributor
@matriv matriv left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Shouldn't we have a changes entry for this?
(Maybe some benchmarks to see if it actually improves performance)

@mfussenegger
Unify optimize calls between top-level select and sub-select
f388324 
Side effect of the change is that this now enables fetch optimization
for sub-queries.
@mfussenegger
Copy link
Member Author

Good call. Looks like this makes it worse in some cases:

Three node cluster

Q: select * from articles where id in (select id from colors where coolness > 0) limit 10000
C: 1
| Version |         Mean ±    Stdev |        Min |     Median |         Q3 |        Max |
|   V1    |       88.784 ±   24.866 |     76.193 |     84.462 |     89.902 |    327.791 |
|   V2    |       92.580 ±   27.771 |     80.273 |     88.493 |     91.625 |    335.170 |
├---------┴-------------------------┴------------┴------------┴------------┴------------┘
|               +   4.19%                           +   4.66%
There is a 84.93% probability that the observed difference is not random, and the best estimate of that difference is 4.19%
The test has no statistical significance

Q: select * from articles where id in (select id from colors where coolness > 0 limit 100) limit 10000
C: 1
| Version |         Mean ±    Stdev |        Min |     Median |         Q3 |        Max |
|   V1    |        4.599 ±    2.740 |      3.106 |      4.121 |      4.438 |     29.793 |
|   V2    |        5.395 ±    1.312 |      4.142 |      5.122 |      5.517 |     14.346 |
├---------┴-------------------------┴------------┴------------┴------------┴------------┘
|               +  15.93%                           +  21.65%
There is a 99.98% probability that the observed difference is not random, and the best estimate of that difference is 15.93%
The test has statistical significance

Q: select * from articles where id in (select id from colors where coolness > 0 limit 10) limit 10000
C: 1
| Version |         Mean ±    Stdev |        Min |     Median |         Q3 |        Max |
|   V1    |        3.274 ±    1.143 |      2.350 |      3.161 |      3.458 |     16.142 |
|   V2    |        2.901 ±    1.261 |      2.202 |      2.683 |      2.993 |     17.781 |
├---------┴-------------------------┴------------┴------------┴------------┴------------┘
|               -  12.08%                           -  16.36%
There is a 99.79% probability that the observed difference is not random, and the best estimate of that difference is 12.08%
The test has statistical significance

Q: select * from articles where id not in (select id from colors where coolness > 0) limit 10000
C: 1
| Version |         Mean ±    Stdev |        Min |     Median |         Q3 |        Max |
|   V1    |      414.548 ±    9.325 |    401.679 |    412.644 |    417.640 |    462.819 |
|   V2    |      424.616 ±   11.068 |    408.753 |    423.037 |    427.560 |    478.425 |
├---------┴-------------------------┴------------┴------------┴------------┴------------┘
|               +   2.40%                           +   2.49%
There is a 100.00% probability that the observed difference is not random, and the best estimate of that difference is 2.40%
The test has statistical significance

Single node cluster

# Results (server side duration in ms)
V1: 5.8.0-e0a9b0b6782cc893871e9cdf1a19d49729e85a1f
V2: 5.8.0-f388324fba1b2ea85de6a3a52d3a17e9476189d5

Q: select * from articles where id in (select id from colors where coolness > 0) limit 10000
C: 1
| Version |         Mean ±    Stdev |        Min |     Median |         Q3 |        Max |
|   V1    |       85.310 ±   22.863 |     79.978 |     82.517 |     84.617 |    399.319 |
|   V2    |       89.104 ±   23.844 |     83.179 |     85.835 |     87.945 |    410.937 |
├---------┴-------------------------┴------------┴------------┴------------┴------------┘
|               +   4.35%                           +   3.94%
There is a 89.49% probability that the observed difference is not random, and the best estimate of that difference is 4.35%
The test has no statistical significance

Q: select * from articles where id in (select id from colors where coolness > 0 limit 100) limit 10000
C: 1
| Version |         Mean ±    Stdev |        Min |     Median |         Q3 |        Max |
|   V1    |        1.902 ±    1.807 |      1.224 |      1.567 |      1.959 |     22.871 |
|   V2    |        2.988 ±    0.906 |      2.306 |      2.761 |      3.176 |     13.415 |
├---------┴-------------------------┴------------┴------------┴------------┴------------┘
|               +  44.41%                           +  55.17%
There is a 100.00% probability that the observed difference is not random, and the best estimate of that difference is 44.41%
The test has statistical significance

Q: select * from articles where id in (select id from colors where coolness > 0 limit 10) limit 10000
C: 1
| Version |         Mean ±    Stdev |        Min |     Median |         Q3 |        Max |
|   V1    |        1.062 ±    0.147 |      0.854 |      1.045 |      1.106 |      2.450 |
|   V2    |        1.185 ±    0.167 |      0.998 |      1.157 |      1.230 |      2.832 |
├---------┴-------------------------┴------------┴------------┴------------┴------------┘
|               +  10.93%                           +  10.19%
There is a 100.00% probability that the observed difference is not random, and the best estimate of that difference is 10.93%
The test has statistical significance

Q: select * from articles where id not in (select id from colors where coolness > 0) limit 10000
C: 1
| Version |         Mean ±    Stdev |        Min |     Median |         Q3 |        Max |
|   V1    |      429.877 ±    9.959 |    421.066 |    428.179 |    431.825 |    546.776 |
|   V2    |      416.736 ±   10.045 |    408.934 |    414.852 |    418.107 |    525.040 |
├---------┴-------------------------┴------------┴------------┴------------┴------------┘
|               -   3.10%                           -   3.16%
There is a 100.00% probability that the observed difference is not random, and the best estimate of that difference is 3.10%
The test has statistical significance

Relates a bit to:

I'll put this on hold for now. After #15548 where we may eliminate some of the differences we can decide on how to proceed here.

@mfussenegger mfussenegger marked this pull request as draft April 8, 2024 09:02
@mfussenegger mfussenegger mentioned this pull request Apr 10, 2024
Closed
5 tasks
@matriv matriv mentioned this pull request Jun 26, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@matriv matriv matriv left review comments

At least 1 approving review is required to merge this pull request.

Assignees
No one assigned
Labels
None yet
Projects
CrateDB 5.10
Status: Could
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
2 participants
@mfussenegger @matriv