Query cache amazon redshift
I ran the same query in a days gap and the execution time is different on the 1st execution run on Amazon Redshift.
I set the cache for the session to FALSE for both runs.
The subsequent queries were faster(ran in milliseconds) for both the queries. I checked the query cache usage and there was no query cache used.
Question:
Why and how did the subsequent queries run faster when no query cache was used?
Why did the 1st execution take different times for the same query?
P.S. I have used same sorting, distribution and column encodings. Attached the image of my benchmarking of execution time.
amazon-redshift
edited Nov 25 '18 at 18:23
SuperShoot
1,778719
asked Nov 25 '18 at 6:13
Success MaharjanSuccess Maharjan
74
add a comment |
I ran the same query in a days gap and the execution time is different on the 1st execution run on Amazon Redshift.
I set the cache for the session to FALSE for both runs.
The subsequent queries were faster(ran in milliseconds) for both the queries. I checked the query cache usage and there was no query cache used.
Question:
Why and how did the subsequent queries run faster when no query cache was used?
Why did the 1st execution take different times for the same query?
P.S. I have used same sorting, distribution and column encodings. Attached the image of my benchmarking of execution time.
amazon-redshift
edited Nov 25 '18 at 18:23
SuperShoot
1,778719
asked Nov 25 '18 at 6:13
Success MaharjanSuccess Maharjan
74
add a comment |
I ran the same query in a days gap and the execution time is different on the 1st execution run on Amazon Redshift.
I set the cache for the session to FALSE for both runs.
The subsequent queries were faster(ran in milliseconds) for both the queries. I checked the query cache usage and there was no query cache used.
Question:
Why and how did the subsequent queries run faster when no query cache was used?
Why did the 1st execution take different times for the same query?
P.S. I have used same sorting, distribution and column encodings. Attached the image of my benchmarking of execution time.
amazon-redshift
edited Nov 25 '18 at 18:23
SuperShoot
1,778719
asked Nov 25 '18 at 6:13
Success MaharjanSuccess Maharjan
74
I ran the same query in a days gap and the execution time is different on the 1st execution run on Amazon Redshift.
I set the cache for the session to FALSE for both runs.
The subsequent queries were faster(ran in milliseconds) for both the queries. I checked the query cache usage and there was no query cache used.
Question:
Why and how did the subsequent queries run faster when no query cache was used?
Why did the 1st execution take different times for the same query?
P.S. I have used same sorting, distribution and column encodings. Attached the image of my benchmarking of execution time.
amazon-redshift
amazon-redshift
edited Nov 25 '18 at 18:23
SuperShoot
1,778719
asked Nov 25 '18 at 6:13
Success MaharjanSuccess Maharjan
74
edited Nov 25 '18 at 18:23
SuperShoot
1,778719
asked Nov 25 '18 at 6:13
Success MaharjanSuccess Maharjan
74
edited Nov 25 '18 at 18:23
SuperShoot
1,778719
edited Nov 25 '18 at 18:23
SuperShoot
1,778719
edited Nov 25 '18 at 18:23
SuperShoot
1,778719
1,778719
asked Nov 25 '18 at 6:13
Success MaharjanSuccess Maharjan
74
asked Nov 25 '18 at 6:13
Success MaharjanSuccess Maharjan
74
asked Nov 25 '18 at 6:13
Success MaharjanSuccess Maharjan
74
74
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Queries (technically query segments) in Redshift are compiled the first time they are executed. Subsequent executions (even with different predicates) usually do not require compilation and will run significantly faster by skipping that step. See the section "Code compilation" in the this document: Factors Affecting Query Performance
Also, please review the document "Compound and Interleaved Sort Keys" in our table design guide before choosing an Interleaved sort key. This type of key is only recommended in specific scenarios and should not be used a default choice.
answered Nov 26 '18 at 14:54
Joe HarrisJoe Harris
7,16112941
1
Hey Joe, Thanks for your reply. I wasn't aware that there was a query compilation cache was involved. Would you happen to know how long such cache is maintained? Regarding the sort keys I read the doc. Thanks for the suggestion, I have removed the interleaved keys and recreated tables with compound sort keys. the execution time does not seem to change though. I will keep to only use the interleaved keys when required. Thanks a lot.
– Success Maharjan
Nov 27 '18 at 8:06
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53465119%2fquery-cache-amazon-redshift%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Queries (technically query segments) in Redshift are compiled the first time they are executed. Subsequent executions (even with different predicates) usually do not require compilation and will run significantly faster by skipping that step. See the section "Code compilation" in the this document: Factors Affecting Query Performance
Also, please review the document "Compound and Interleaved Sort Keys" in our table design guide before choosing an Interleaved sort key. This type of key is only recommended in specific scenarios and should not be used a default choice.
answered Nov 26 '18 at 14:54
Joe HarrisJoe Harris
7,16112941
1
Hey Joe, Thanks for your reply. I wasn't aware that there was a query compilation cache was involved. Would you happen to know how long such cache is maintained? Regarding the sort keys I read the doc. Thanks for the suggestion, I have removed the interleaved keys and recreated tables with compound sort keys. the execution time does not seem to change though. I will keep to only use the interleaved keys when required. Thanks a lot.
– Success Maharjan
Nov 27 '18 at 8:06
add a comment |
Queries (technically query segments) in Redshift are compiled the first time they are executed. Subsequent executions (even with different predicates) usually do not require compilation and will run significantly faster by skipping that step. See the section "Code compilation" in the this document: Factors Affecting Query Performance
Also, please review the document "Compound and Interleaved Sort Keys" in our table design guide before choosing an Interleaved sort key. This type of key is only recommended in specific scenarios and should not be used a default choice.
answered Nov 26 '18 at 14:54
Joe HarrisJoe Harris
7,16112941
1
Hey Joe, Thanks for your reply. I wasn't aware that there was a query compilation cache was involved. Would you happen to know how long such cache is maintained? Regarding the sort keys I read the doc. Thanks for the suggestion, I have removed the interleaved keys and recreated tables with compound sort keys. the execution time does not seem to change though. I will keep to only use the interleaved keys when required. Thanks a lot.
– Success Maharjan
Nov 27 '18 at 8:06
add a comment |
Queries (technically query segments) in Redshift are compiled the first time they are executed. Subsequent executions (even with different predicates) usually do not require compilation and will run significantly faster by skipping that step. See the section "Code compilation" in the this document: Factors Affecting Query Performance
Also, please review the document "Compound and Interleaved Sort Keys" in our table design guide before choosing an Interleaved sort key. This type of key is only recommended in specific scenarios and should not be used a default choice.
answered Nov 26 '18 at 14:54
Joe HarrisJoe Harris
7,16112941
Queries (technically query segments) in Redshift are compiled the first time they are executed. Subsequent executions (even with different predicates) usually do not require compilation and will run significantly faster by skipping that step. See the section "Code compilation" in the this document: Factors Affecting Query Performance
Also, please review the document "Compound and Interleaved Sort Keys" in our table design guide before choosing an Interleaved sort key. This type of key is only recommended in specific scenarios and should not be used a default choice.
answered Nov 26 '18 at 14:54
Joe HarrisJoe Harris
7,16112941
answered Nov 26 '18 at 14:54
Joe HarrisJoe Harris
7,16112941
answered Nov 26 '18 at 14:54
Joe HarrisJoe Harris
7,16112941
answered Nov 26 '18 at 14:54
Joe HarrisJoe Harris
7,16112941
7,16112941
1
Hey Joe, Thanks for your reply. I wasn't aware that there was a query compilation cache was involved. Would you happen to know how long such cache is maintained? Regarding the sort keys I read the doc. Thanks for the suggestion, I have removed the interleaved keys and recreated tables with compound sort keys. the execution time does not seem to change though. I will keep to only use the interleaved keys when required. Thanks a lot.
– Success Maharjan
Nov 27 '18 at 8:06
add a comment |
1
Hey Joe, Thanks for your reply. I wasn't aware that there was a query compilation cache was involved. Would you happen to know how long such cache is maintained? Regarding the sort keys I read the doc. Thanks for the suggestion, I have removed the interleaved keys and recreated tables with compound sort keys. the execution time does not seem to change though. I will keep to only use the interleaved keys when required. Thanks a lot.
– Success Maharjan
Nov 27 '18 at 8:06
1
1
Hey Joe, Thanks for your reply. I wasn't aware that there was a query compilation cache was involved. Would you happen to know how long such cache is maintained? Regarding the sort keys I read the doc. Thanks for the suggestion, I have removed the interleaved keys and recreated tables with compound sort keys. the execution time does not seem to change though. I will keep to only use the interleaved keys when required. Thanks a lot.
– Success Maharjan
Nov 27 '18 at 8:06
Hey Joe, Thanks for your reply. I wasn't aware that there was a query compilation cache was involved. Would you happen to know how long such cache is maintained? Regarding the sort keys I read the doc. Thanks for the suggestion, I have removed the interleaved keys and recreated tables with compound sort keys. the execution time does not seem to change though. I will keep to only use the interleaved keys when required. Thanks a lot.
– Success Maharjan
Nov 27 '18 at 8:06
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53465119%2fquery-cache-amazon-redshift%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
