Skip to content
Snippets Groups Projects

Resolve "Create index-checks package"

Merged Resolve "Create index-checks package"
43-create-index-checks-package into main
Merged James Page requested to merge 43-create-index-checks-package into main
Compare
and
2 files
+ 297
− 0
Compare changes
  • Side-by-side
  • Inline
Files
2
db_utils/db_queries.go 0 → 100644
+ 223
− 0
package db_utils
const TableIndexStatusQuery = `
SELECT
relname AS table_name,
CASE WHEN seq_scan + idx_scan = 0 THEN 0
ELSE 100 * idx_scan / (seq_scan + idx_scan)
END AS percent_usage,
n_live_tup AS rows_in_table,
CASE
-- List tables without rows separately.
WHEN n_live_tup = 0 THEN ?
-- Problem indices.
WHEN seq_scan + idx_scan = 0 AND n_live_tup > 0 THEN ?
WHEN 100 * idx_scan / (seq_scan + idx_scan) < 98 THEN ?
-- Everything else is considered healthy.
ELSE ?
END
AS status
FROM
pg_stat_user_tables
ORDER BY
n_live_tup DESC;
`
const ProblemIndexQuery = `
WITH table_scans AS (
SELECT
relid,
tables.idx_scan + tables.seq_scan AS all_scans,
(
tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del
) AS writes,
pg_relation_size(relid) AS table_size
FROM
pg_stat_user_tables AS TABLES
),
all_writes AS (
SELECT
sum(writes) AS total_writes
FROM
table_scans
),
indexes AS (
SELECT
idx_stat.relid,
idx_stat.indexrelid,
idx_stat.schemaname AS schema_name,
idx_stat.relname AS table_name,
idx_stat.indexrelname AS index_name,
idx_stat.idx_scan,
pg_relation_size(idx_stat.indexrelid) AS index_bytes,
indexdef ~* 'USING btree' AS idx_is_btree
FROM
pg_stat_user_indexes AS idx_stat
JOIN pg_index
USING (indexrelid)
JOIN pg_indexes AS INDEXES
ON
idx_stat.schemaname = indexes.schemaname
AND idx_stat.relname = indexes.tablename
AND idx_stat.indexrelname = indexes.indexname
WHERE
pg_index.indisunique = FALSE
),
index_ratios AS (
SELECT
schema_name,
table_name,
index_name,
idx_scan,
all_scans,
round(
(
CASE
WHEN all_scans = 0 THEN 0.0::NUMERIC
ELSE idx_scan::NUMERIC / all_scans * 100
END
),
2
) AS index_scan_percentage,
writes,
round(
(
CASE
WHEN writes = 0 THEN idx_scan::NUMERIC
ELSE idx_scan::NUMERIC / writes
END
),
2
)
AS scans_per_write,
pg_size_pretty(index_bytes) AS index_size,
pg_size_pretty(table_size) AS table_size,
idx_is_btree,
index_bytes
FROM
INDEXES
JOIN table_scans
USING (relid)
),
index_groups AS (
SELECT
-- Never-used indexes
? AS reason,
*,
1 AS grp
FROM
index_ratios
WHERE
idx_scan = 0
AND idx_is_btree
UNION ALL
SELECT
-- Low scans, high writes
? AS reason,
*,
2 AS grp
FROM
index_ratios
WHERE
scans_per_write <= 1
AND index_scan_percentage < 10
AND idx_scan > 0
AND writes > 100
AND idx_is_btree
UNION ALL
SELECT
-- Seldom Used Large Indexes
? AS reason,
*,
3 AS grp
FROM
index_ratios
WHERE
index_scan_percentage < 5
AND scans_per_write > 1
AND idx_scan > 0
AND idx_is_btree
AND index_bytes > 100000000
UNION ALL
SELECT
-- High-Write Large Non-Btree
? AS reason,
index_ratios.*,
4 AS grp
FROM
index_ratios,
all_writes
WHERE
(
writes::NUMERIC / (
total_writes + 1
)
) > 0.02
AND NOT idx_is_btree
AND index_bytes > 100000000
ORDER BY
grp,
index_bytes DESC
)
SELECT
reason,
schema_name,
table_name,
index_name,
index_scan_percentage,
scans_per_write,
index_size,
table_size
FROM
index_groups
;
`
const ExistingIndexQuery = `
SELECT
c.relname AS index_name,
c.relpages AS index_size
FROM
pg_index i
JOIN pg_opclass op ON
i.indclass[0] = op.oid
JOIN pg_am am ON
op.opcmethod = am.oid
JOIN pg_class c ON
i.indexrelid = c.oid
JOIN pg_namespace n ON
c.relnamespace = n.oid
WHERE
am.amname = 'btree'
AND n.nspname = 'public'
-- Don't check temp tables, which may be from another session:
AND c.relpersistence != 't'
-- Function may throw an error when this is omitted:
AND c.relkind = 'i'
AND i.indisready
AND i.indisvalid
ORDER BY
c.relpages DESC
;
`
const CorruptIndexQuery = `SELECT
bt_index_check(index => c.oid, heapallindexed => i.indisunique) AS check_result,
c.relname AS relation_name,
c.relpages AS relation_size
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
-- Only btree indices are currently supported by the index-checking function.
WHERE am.amname = 'btree'
-- We only analyse our own indices.
AND n.nspname = 'public'
AND relname IN (?)
-- Don't check temp tables, which may be from another session.
AND c.relpersistence != 't'
-- Function may throw an error when this is omitted.
AND c.relkind = 'i' AND i.indisready AND i.indisvalid
ORDER BY c.relpages DESC
;`