- Notifications
You must be signed in to change notification settings - Fork805
feat: add support for pg_stat_user_indexes, pg_index_properties, pg_index_size_bytes#1071
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 ourterms of service andprivacy statement. We’ll occasionally send you account related emails.
Already on GitHub?Sign in to your account
base:master
Are you sure you want to change the base?
Uh oh!
There was an error while loading.Please reload this page.
Conversation
299ac3f toa0dceabCompareSigned-off-by: Michael Todorovic <michael.todorovic@outlook.com>
Signed-off-by: Michael Todorovic <michael.todorovic@outlook.com>
2606e0a to69223b3CompareSigned-off-by: Michael Todorovic <michael.todorovic@outlook.com>
michael-todorovic commentedOct 11, 2024
You can identify unused indexes with this promql: It searches for production indexes scanned less than 15 times, which are not primary keys (you need to keep those indexes for PG itself) and that are bigger than 100MB The metrics themselves look like: |
Signed-off-by: Michael Todorovic <michael.todorovic@outlook.com>
chris-downs commentedDec 19, 2024
To give this a bump, we are now using this branch in production with no issues and it's very useful. It would be great to get it into mainline. |
sysadmind left a comment
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
In addition to the comments on the code, all of the dates for the Copyright need to be updated to the current year for the files that you have created. I think the guys are good here, my feedback is mostly about being able to maintain this code long term.
| "fmt" | ||
| "strings" | ||
| "log/slog" |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
I'm not sure why this import is in it's own section. This should go with the stdlib imports above.
| ) | ||
| func pgIndexQuery(columns []string) string { | ||
| return fmt.Sprintf("SELECT %s FROM pg_catalog.pg_stat_user_indexes s JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid WHERE i.indislive='1';", strings.Join(columns, ",")) |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
We don't typically use a function to build the query unless we need dynamic columns. In this case it should just be a static query const.
| var idxIsUnique, idxIsPrimary, idxIsValid, idxIsReady sql.NullBool | ||
| var idxSize sql.NullFloat64 | ||
| r := []any{ |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
I don't think that this slice adds anything. We can scan directly into the vars above.
| if err := rows.Scan(r...); err != nil { | ||
| return err | ||
| } | ||
| datnameLabel := "unknown" |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
Do these metrics make sense when these values are not known? I feel that if too many are NULL/unknown, the metric is useless and should just be skipped.
| propertiesLabels..., | ||
| ) | ||
| sizeLabels := []string{datnameLabel, schemanameLabel, relnameLabel, indexrelnameLabel} |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
Again, what does the slice improve vs just using the values below?
| indexSizeMetric = idxSize.Float64 | ||
| } | ||
| propertiesLabels := []string{datnameLabel, schemanameLabel, relnameLabel, indexrelnameLabel, indexIsUniqueLabel, indexIsPrimaryLabel, indexIsValidLabel, indexIsReadyLabel} |
There was a problem hiding this comment.
Choose a reason for hiding this comment
The reason will be displayed to describe this comment to others.Learn more.
Again, what does the slice improve vs just using the values below?
This PR adds support for
pg_stat_user_indexesin order to get scan, read, fetch statistics about indexes. This can be used to find out unused/low-used indexesThis relates to issue#1065