Select count(*) from `pg_table_def` takes minutes on Amazon Redshiftconcurrent query performance in amazon redshiftAmazon Redshift for SaaS applicationEfficient GROUP BY a CASE expression in Amazon Redshift/PostgreSQLRedshift SELECT * performance versus COUNT(*) for non existent rowAmazon redshift: bulk insert vs COPYing from s3Amazon Redshift doing Hash Join even when joined on column that is both Dist Key and Sort KeyRedshift select * vs select single columnis “INSERT INTO SELECT” free from race conditions in redshiftRedshift DELETE using slow Hash Join while equivalent SELECT uses Merge JoinSelect from a wide table in redshift with where clause on any column
If you inherit a Roth 401(k), is it taxed?
Omnidirectional LED, is it possible?
Convert graph format for Mathematica graph functions
What force enables us to walk? Friction or normal reaction?
Would people understand me speaking German all over Europe?
How well would the Moon protect the Earth from an Asteroid?
Why was the LRV's speed gauge displaying metric units?
GNU sort stable sort when sort does not know sort order
Semen retention is a important thing in Martial arts?
How did astronauts using rovers tell direction without compasses on the Moon?
How to improve king safety
Why were contact sensors put on three of the Lunar Module's four legs? Did they ever bend and stick out sideways?
Do the books ever say oliphaunts aren’t elephants?
Problem with Eigenvectors
My employer is refusing to give me the pay that was advertised after an internal job move
Nuclear breeder/reactor plant controlled by two A.I. makes too much power
Can this party play the Lost Mine of Phandelver adventure without too much trouble?
Composing fill in the blanks
Why does the Eurostar not show youth pricing?
Did Vladimir Lenin have a cat?
Why is it "on the inside" and not "in the inside"?
What language is Raven using for her attack in the new 52?
How can Paypal know my card is being used in another account?
Wrapping IMemoryCache with SemaphoreSlim
Select count(*) from `pg_table_def` takes minutes on Amazon Redshift
concurrent query performance in amazon redshiftAmazon Redshift for SaaS applicationEfficient GROUP BY a CASE expression in Amazon Redshift/PostgreSQLRedshift SELECT * performance versus COUNT(*) for non existent rowAmazon redshift: bulk insert vs COPYing from s3Amazon Redshift doing Hash Join even when joined on column that is both Dist Key and Sort KeyRedshift select * vs select single columnis “INSERT INTO SELECT” free from race conditions in redshiftRedshift DELETE using slow Hash Join while equivalent SELECT uses Merge JoinSelect from a wide table in redshift with where clause on any column
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
Why might a count from pg_table_def in Redshift take more than 2 minutes?
I'm debugging a consistently slow query (exceeds a 4 minute timeout) that lists all tables and columns in a certain schema. However, the slowness is reproducible with the simpler query below:
redshift-psql=> SELECT COUNT(*) from pg_table_def;
count
-------
5067
(1 row)
Time: 144956.109 ms
amazon-web-services amazon-redshift
asked Mar 26 at 20:33
JoeJoe
1,0611 gold badge14 silver badges34 bronze badges
add a comment |
Why might a count from pg_table_def in Redshift take more than 2 minutes?
I'm debugging a consistently slow query (exceeds a 4 minute timeout) that lists all tables and columns in a certain schema. However, the slowness is reproducible with the simpler query below:
redshift-psql=> SELECT COUNT(*) from pg_table_def;
count
-------
5067
(1 row)
Time: 144956.109 ms
amazon-web-services amazon-redshift
asked Mar 26 at 20:33
JoeJoe
1,0611 gold badge14 silver badges34 bronze badges
I am guessing network plays a role in your case. You need to take a look it from that aspect, I have ran the same query on my Redshift cluster and it took 0.18 seconds
– mdem7
Mar 26 at 22:24
Also the workload at the same time you are running this query on the cluster might play a role
– mdem7
Mar 26 at 23:20
You need to understand how WLM works on Redshift - that query time also includes time the query is potentially queued waiting for a query slot. As per above comment, if you have a lot of concurrent queries (or very few WLM slots) then the query could be queued waiting for other queries to complete. Does your cluster have SQA (short query acceleration) enabled?
– Nathan Griffiths
Mar 29 at 0:44
add a comment |
Why might a count from pg_table_def in Redshift take more than 2 minutes?
I'm debugging a consistently slow query (exceeds a 4 minute timeout) that lists all tables and columns in a certain schema. However, the slowness is reproducible with the simpler query below:
redshift-psql=> SELECT COUNT(*) from pg_table_def;
count
-------
5067
(1 row)
Time: 144956.109 ms
amazon-web-services amazon-redshift
asked Mar 26 at 20:33
JoeJoe
1,0611 gold badge14 silver badges34 bronze badges
Why might a count from pg_table_def in Redshift take more than 2 minutes?
I'm debugging a consistently slow query (exceeds a 4 minute timeout) that lists all tables and columns in a certain schema. However, the slowness is reproducible with the simpler query below:
redshift-psql=> SELECT COUNT(*) from pg_table_def;
count
-------
5067
(1 row)
Time: 144956.109 ms
amazon-web-services amazon-redshift
amazon-web-services amazon-redshift
asked Mar 26 at 20:33
JoeJoe
1,0611 gold badge14 silver badges34 bronze badges
asked Mar 26 at 20:33
JoeJoe
1,0611 gold badge14 silver badges34 bronze badges
asked Mar 26 at 20:33
JoeJoe
1,0611 gold badge14 silver badges34 bronze badges
asked Mar 26 at 20:33
JoeJoe
1,0611 gold badge14 silver badges34 bronze badges
asked Mar 26 at 20:33
JoeJoe
1,0611 gold badge14 silver badges34 bronze badges
1,0611 gold badge14 silver badges34 bronze badges
I am guessing network plays a role in your case. You need to take a look it from that aspect, I have ran the same query on my Redshift cluster and it took 0.18 seconds
– mdem7
Mar 26 at 22:24
Also the workload at the same time you are running this query on the cluster might play a role
– mdem7
Mar 26 at 23:20
You need to understand how WLM works on Redshift - that query time also includes time the query is potentially queued waiting for a query slot. As per above comment, if you have a lot of concurrent queries (or very few WLM slots) then the query could be queued waiting for other queries to complete. Does your cluster have SQA (short query acceleration) enabled?
– Nathan Griffiths
Mar 29 at 0:44
add a comment |
I am guessing network plays a role in your case. You need to take a look it from that aspect, I have ran the same query on my Redshift cluster and it took 0.18 seconds
– mdem7
Mar 26 at 22:24
Also the workload at the same time you are running this query on the cluster might play a role
– mdem7
Mar 26 at 23:20
You need to understand how WLM works on Redshift - that query time also includes time the query is potentially queued waiting for a query slot. As per above comment, if you have a lot of concurrent queries (or very few WLM slots) then the query could be queued waiting for other queries to complete. Does your cluster have SQA (short query acceleration) enabled?
– Nathan Griffiths
Mar 29 at 0:44
I am guessing network plays a role in your case. You need to take a look it from that aspect, I have ran the same query on my Redshift cluster and it took 0.18 seconds
– mdem7
Mar 26 at 22:24
I am guessing network plays a role in your case. You need to take a look it from that aspect, I have ran the same query on my Redshift cluster and it took 0.18 seconds
– mdem7
Mar 26 at 22:24
Also the workload at the same time you are running this query on the cluster might play a role
– mdem7
Mar 26 at 23:20
Also the workload at the same time you are running this query on the cluster might play a role
– mdem7
Mar 26 at 23:20
You need to understand how WLM works on Redshift - that query time also includes time the query is potentially queued waiting for a query slot. As per above comment, if you have a lot of concurrent queries (or very few WLM slots) then the query could be queued waiting for other queries to complete. Does your cluster have SQA (short query acceleration) enabled?
– Nathan Griffiths
Mar 29 at 0:44
You need to understand how WLM works on Redshift - that query time also includes time the query is potentially queued waiting for a query slot. As per above comment, if you have a lot of concurrent queries (or very few WLM slots) then the query could be queued waiting for other queries to complete. Does your cluster have SQA (short query acceleration) enabled?
– Nathan Griffiths
Mar 29 at 0:44
add a comment |
0
active
oldest
votes
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%2f55365779%2fselect-count-from-pg-table-def-takes-minutes-on-amazon-redshift%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Is this question similar to what you get asked at work? Learn more about asking and sharing private information with your coworkers using Stack Overflow for Teams.
Is this question similar to what you get asked at work? Learn more about asking and sharing private information with your coworkers using Stack Overflow for Teams.
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%2f55365779%2fselect-count-from-pg-table-def-takes-minutes-on-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
I am guessing network plays a role in your case. You need to take a look it from that aspect, I have ran the same query on my Redshift cluster and it took 0.18 seconds
– mdem7
Mar 26 at 22:24
Also the workload at the same time you are running this query on the cluster might play a role
– mdem7
Mar 26 at 23:20
You need to understand how WLM works on Redshift - that query time also includes time the query is potentially queued waiting for a query slot. As per above comment, if you have a lot of concurrent queries (or very few WLM slots) then the query could be queued waiting for other queries to complete. Does your cluster have SQA (short query acceleration) enabled?
– Nathan Griffiths
Mar 29 at 0:44