How to tell when a Postgres table was clustered and what indexes were used
I've been impressed by the performance improvements achieved with clustering, but not with how long it takes.
I know clustering needs to be rebuilt if a table or partition is changed after the clustering, but unless I've made a note of when I last clustered a table, how can I tell when I need to do it again?
I can use this query to tell me what table(s) have one or more clustered indexes
SELECT *
FROM pg_class c
JOIN pg_index i ON i.indrelid = c.oid
WHERE relkind = 'r' AND relhasindex AND i.indisclustered
My questions are.
- How can I tell which indexes have been clustered?
- Is there any way of finding out exactly when a table was last clustered?
- How can I tell if a clustered index is still 'valid', or in other words, how can tell how much a table/index has changed enough that I need to re-build the cluster.
I've noticed that it takes just as long to re-build a clustered index as it does to build it in the first place (even if the table hasn't been touched in the meantime). So I want to avoid re-clustering unless I know the table needs it.
UPDATE for clarity (I hope)
If I use this command....
CLUSTER tableA USING tableA_idx1;
- How can I find out at a later date which index was referenced i.e.
tableA_idx1 (the table has multiple indexes defined)? - Is it recorded anywhere when this command was run?
- I know that the cluster may need to be rebuilt/refreshed/recreated (not sure of the correct phraseology) occasionally using CLUSTER tableA when the table undergoes changes. Is there anyway of knowing when the table has changed so much that the clustering no longer helps?
postgresql query-performance
edited Nov 14 '18 at 15:19
Laurenz Albe
47.4k102748
asked Nov 14 '18 at 11:58
HemelHemel
1998
add a comment |
I've been impressed by the performance improvements achieved with clustering, but not with how long it takes.
I know clustering needs to be rebuilt if a table or partition is changed after the clustering, but unless I've made a note of when I last clustered a table, how can I tell when I need to do it again?
I can use this query to tell me what table(s) have one or more clustered indexes
SELECT *
FROM pg_class c
JOIN pg_index i ON i.indrelid = c.oid
WHERE relkind = 'r' AND relhasindex AND i.indisclustered
My questions are.
- How can I tell which indexes have been clustered?
- Is there any way of finding out exactly when a table was last clustered?
- How can I tell if a clustered index is still 'valid', or in other words, how can tell how much a table/index has changed enough that I need to re-build the cluster.
I've noticed that it takes just as long to re-build a clustered index as it does to build it in the first place (even if the table hasn't been touched in the meantime). So I want to avoid re-clustering unless I know the table needs it.
UPDATE for clarity (I hope)
If I use this command....
CLUSTER tableA USING tableA_idx1;
- How can I find out at a later date which index was referenced i.e.
tableA_idx1 (the table has multiple indexes defined)? - Is it recorded anywhere when this command was run?
- I know that the cluster may need to be rebuilt/refreshed/recreated (not sure of the correct phraseology) occasionally using CLUSTER tableA when the table undergoes changes. Is there anyway of knowing when the table has changed so much that the clustering no longer helps?
postgresql query-performance
edited Nov 14 '18 at 15:19
Laurenz Albe
47.4k102748
asked Nov 14 '18 at 11:58
HemelHemel
1998
"How can I tell which indexes have been clustered?" makes not sense. An index is never "clustered" in Postgres. If you want find out which indexes were used for aclustercommand, then your query will already do that (as it only returns tables that have been "clustered" by an index) Also there is no such thing as "a cluster" in Postgres, so you can't "rebuild the cluster".clustersimply re-arranges the rows in the table to have the same physical order as the the index entries.
– a_horse_with_no_name
Nov 14 '18 at 14:47
add a comment |
I've been impressed by the performance improvements achieved with clustering, but not with how long it takes.
I know clustering needs to be rebuilt if a table or partition is changed after the clustering, but unless I've made a note of when I last clustered a table, how can I tell when I need to do it again?
I can use this query to tell me what table(s) have one or more clustered indexes
SELECT *
FROM pg_class c
JOIN pg_index i ON i.indrelid = c.oid
WHERE relkind = 'r' AND relhasindex AND i.indisclustered
My questions are.
- How can I tell which indexes have been clustered?
- Is there any way of finding out exactly when a table was last clustered?
- How can I tell if a clustered index is still 'valid', or in other words, how can tell how much a table/index has changed enough that I need to re-build the cluster.
I've noticed that it takes just as long to re-build a clustered index as it does to build it in the first place (even if the table hasn't been touched in the meantime). So I want to avoid re-clustering unless I know the table needs it.
UPDATE for clarity (I hope)
If I use this command....
CLUSTER tableA USING tableA_idx1;
- How can I find out at a later date which index was referenced i.e.
tableA_idx1 (the table has multiple indexes defined)? - Is it recorded anywhere when this command was run?
- I know that the cluster may need to be rebuilt/refreshed/recreated (not sure of the correct phraseology) occasionally using CLUSTER tableA when the table undergoes changes. Is there anyway of knowing when the table has changed so much that the clustering no longer helps?
postgresql query-performance
edited Nov 14 '18 at 15:19
Laurenz Albe
47.4k102748
asked Nov 14 '18 at 11:58
HemelHemel
1998
I've been impressed by the performance improvements achieved with clustering, but not with how long it takes.
I know clustering needs to be rebuilt if a table or partition is changed after the clustering, but unless I've made a note of when I last clustered a table, how can I tell when I need to do it again?
I can use this query to tell me what table(s) have one or more clustered indexes
SELECT *
FROM pg_class c
JOIN pg_index i ON i.indrelid = c.oid
WHERE relkind = 'r' AND relhasindex AND i.indisclustered
My questions are.
- How can I tell which indexes have been clustered?
- Is there any way of finding out exactly when a table was last clustered?
- How can I tell if a clustered index is still 'valid', or in other words, how can tell how much a table/index has changed enough that I need to re-build the cluster.
I've noticed that it takes just as long to re-build a clustered index as it does to build it in the first place (even if the table hasn't been touched in the meantime). So I want to avoid re-clustering unless I know the table needs it.
UPDATE for clarity (I hope)
If I use this command....
CLUSTER tableA USING tableA_idx1;
- How can I find out at a later date which index was referenced i.e.
tableA_idx1 (the table has multiple indexes defined)? - Is it recorded anywhere when this command was run?
- I know that the cluster may need to be rebuilt/refreshed/recreated (not sure of the correct phraseology) occasionally using CLUSTER tableA when the table undergoes changes. Is there anyway of knowing when the table has changed so much that the clustering no longer helps?
postgresql query-performance
postgresql query-performance
edited Nov 14 '18 at 15:19
Laurenz Albe
47.4k102748
asked Nov 14 '18 at 11:58
HemelHemel
1998
edited Nov 14 '18 at 15:19
Laurenz Albe
47.4k102748
asked Nov 14 '18 at 11:58
HemelHemel
1998
edited Nov 14 '18 at 15:19
Laurenz Albe
47.4k102748
edited Nov 14 '18 at 15:19
Laurenz Albe
47.4k102748
edited Nov 14 '18 at 15:19
Laurenz Albe
47.4k102748
47.4k102748
asked Nov 14 '18 at 11:58
HemelHemel
1998
asked Nov 14 '18 at 11:58
HemelHemel
1998
asked Nov 14 '18 at 11:58
HemelHemel
1998
1998
"How can I tell which indexes have been clustered?" makes not sense. An index is never "clustered" in Postgres. If you want find out which indexes were used for aclustercommand, then your query will already do that (as it only returns tables that have been "clustered" by an index) Also there is no such thing as "a cluster" in Postgres, so you can't "rebuild the cluster".clustersimply re-arranges the rows in the table to have the same physical order as the the index entries.
– a_horse_with_no_name
Nov 14 '18 at 14:47
add a comment |
"How can I tell which indexes have been clustered?" makes not sense. An index is never "clustered" in Postgres. If you want find out which indexes were used for aclustercommand, then your query will already do that (as it only returns tables that have been "clustered" by an index) Also there is no such thing as "a cluster" in Postgres, so you can't "rebuild the cluster".clustersimply re-arranges the rows in the table to have the same physical order as the the index entries.
– a_horse_with_no_name
Nov 14 '18 at 14:47
"How can I tell which indexes have been clustered?" makes not sense. An index is never "clustered" in Postgres. If you want find out which indexes were used for a
cluster command, then your query will already do that (as it only returns tables that have been "clustered" by an index) Also there is no such thing as "a cluster" in Postgres, so you can't "rebuild the cluster". cluster simply re-arranges the rows in the table to have the same physical order as the the index entries.– a_horse_with_no_name
Nov 14 '18 at 14:47
"How can I tell which indexes have been clustered?" makes not sense. An index is never "clustered" in Postgres. If you want find out which indexes were used for a
cluster command, then your query will already do that (as it only returns tables that have been "clustered" by an index) Also there is no such thing as "a cluster" in Postgres, so you can't "rebuild the cluster". cluster simply re-arranges the rows in the table to have the same physical order as the the index entries.– a_horse_with_no_name
Nov 14 '18 at 14:47
add a comment |
1 Answer
1
active
oldest
votes
To tell which index was last used to cluster the table, use the pg_index system catalog.
Query the table for all indexes that belong to your table and see which one has indisclustered set. A table can only be clustered by a single index at a time.
There is no way to find out when the table was last clustered, but that's not very interesting anyway. What you want to know is how good the clustering still is.
To find that, query the pg_stats line for the column on which you clustered. If correlation is close to 1, you are still good. The smaller the value gets, the more clustering is indicated.
answered Nov 14 '18 at 15:16
Laurenz AlbeLaurenz Albe
47.4k102748
@Laurenze Albe - thank you! The reason I asked about when the table was clustered is that in the absence of better indicators, it gives me a way of estimating the likelihood of the cluster still being 'good' - using the correlation you mention seems a much better way!
– Hemel
Nov 14 '18 at 15:25
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%2f53299733%2fhow-to-tell-when-a-postgres-table-was-clustered-and-what-indexes-were-used%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
To tell which index was last used to cluster the table, use the pg_index system catalog.
Query the table for all indexes that belong to your table and see which one has indisclustered set. A table can only be clustered by a single index at a time.
There is no way to find out when the table was last clustered, but that's not very interesting anyway. What you want to know is how good the clustering still is.
To find that, query the pg_stats line for the column on which you clustered. If correlation is close to 1, you are still good. The smaller the value gets, the more clustering is indicated.
answered Nov 14 '18 at 15:16
Laurenz AlbeLaurenz Albe
47.4k102748
@Laurenze Albe - thank you! The reason I asked about when the table was clustered is that in the absence of better indicators, it gives me a way of estimating the likelihood of the cluster still being 'good' - using the correlation you mention seems a much better way!
– Hemel
Nov 14 '18 at 15:25
add a comment |
To tell which index was last used to cluster the table, use the pg_index system catalog.
Query the table for all indexes that belong to your table and see which one has indisclustered set. A table can only be clustered by a single index at a time.
There is no way to find out when the table was last clustered, but that's not very interesting anyway. What you want to know is how good the clustering still is.
To find that, query the pg_stats line for the column on which you clustered. If correlation is close to 1, you are still good. The smaller the value gets, the more clustering is indicated.
answered Nov 14 '18 at 15:16
Laurenz AlbeLaurenz Albe
47.4k102748
@Laurenze Albe - thank you! The reason I asked about when the table was clustered is that in the absence of better indicators, it gives me a way of estimating the likelihood of the cluster still being 'good' - using the correlation you mention seems a much better way!
– Hemel
Nov 14 '18 at 15:25
add a comment |
To tell which index was last used to cluster the table, use the pg_index system catalog.
Query the table for all indexes that belong to your table and see which one has indisclustered set. A table can only be clustered by a single index at a time.
There is no way to find out when the table was last clustered, but that's not very interesting anyway. What you want to know is how good the clustering still is.
To find that, query the pg_stats line for the column on which you clustered. If correlation is close to 1, you are still good. The smaller the value gets, the more clustering is indicated.
answered Nov 14 '18 at 15:16
Laurenz AlbeLaurenz Albe
47.4k102748
To tell which index was last used to cluster the table, use the pg_index system catalog.
Query the table for all indexes that belong to your table and see which one has indisclustered set. A table can only be clustered by a single index at a time.
There is no way to find out when the table was last clustered, but that's not very interesting anyway. What you want to know is how good the clustering still is.
To find that, query the pg_stats line for the column on which you clustered. If correlation is close to 1, you are still good. The smaller the value gets, the more clustering is indicated.
answered Nov 14 '18 at 15:16
Laurenz AlbeLaurenz Albe
47.4k102748
answered Nov 14 '18 at 15:16
Laurenz AlbeLaurenz Albe
47.4k102748
answered Nov 14 '18 at 15:16
Laurenz AlbeLaurenz Albe
47.4k102748
answered Nov 14 '18 at 15:16
Laurenz AlbeLaurenz Albe
47.4k102748
47.4k102748
@Laurenze Albe - thank you! The reason I asked about when the table was clustered is that in the absence of better indicators, it gives me a way of estimating the likelihood of the cluster still being 'good' - using the correlation you mention seems a much better way!
– Hemel
Nov 14 '18 at 15:25
add a comment |
@Laurenze Albe - thank you! The reason I asked about when the table was clustered is that in the absence of better indicators, it gives me a way of estimating the likelihood of the cluster still being 'good' - using the correlation you mention seems a much better way!
– Hemel
Nov 14 '18 at 15:25
@Laurenze Albe - thank you! The reason I asked about when the table was clustered is that in the absence of better indicators, it gives me a way of estimating the likelihood of the cluster still being 'good' - using the correlation you mention seems a much better way!
– Hemel
Nov 14 '18 at 15:25
@Laurenze Albe - thank you! The reason I asked about when the table was clustered is that in the absence of better indicators, it gives me a way of estimating the likelihood of the cluster still being 'good' - using the correlation you mention seems a much better way!
– Hemel
Nov 14 '18 at 15:25
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%2f53299733%2fhow-to-tell-when-a-postgres-table-was-clustered-and-what-indexes-were-used%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
"How can I tell which indexes have been clustered?" makes not sense. An index is never "clustered" in Postgres. If you want find out which indexes were used for a
clustercommand, then your query will already do that (as it only returns tables that have been "clustered" by an index) Also there is no such thing as "a cluster" in Postgres, so you can't "rebuild the cluster".clustersimply re-arranges the rows in the table to have the same physical order as the the index entries.– a_horse_with_no_name
Nov 14 '18 at 14:47