/**
* Indexes Resource
*
* Index usage statistics with unused/rarely-used detection and DROP recommendations.
*/
import type { PostgresAdapter } from '../PostgresAdapter.js';
import type { ResourceDefinition, RequestContext } from '../../../types/index.js';
interface IndexRecommendation {
type: 'UNUSED_INDEX' | 'RARELY_USED' | 'RECENTLY_CREATED' | 'EMPTY_TABLE' | 'HEALTHY';
priority?: 'HIGH' | 'MEDIUM' | 'LOW' | 'INFO';
index?: string;
table?: string;
size?: string;
scans?: number;
tableRows?: number;
action?: string;
benefit?: string;
message?: string;
}
interface IndexRow {
schemaname: string;
tablename: string;
indexname: string;
index_scans: number;
tuples_read: number;
tuples_fetched: number;
index_size: string;
size_bytes: number;
last_idx_scan: string | null;
potentially_new: boolean;
table_rows: number;
}
export function createIndexesResource(adapter: PostgresAdapter): ResourceDefinition {
return {
uri: 'postgres://indexes',
name: 'Index Statistics',
description: 'Index usage statistics with unused/rarely-used detection and DROP recommendations',
mimeType: 'application/json',
handler: async (_uri: string, _context: RequestContext) => {
// Get index usage statistics including last scan time and table row count
const indexResult = await adapter.executeQuery(`
SELECT
sui.schemaname,
sui.relname as tablename,
sui.indexrelname as indexname,
sui.idx_scan as index_scans,
sui.idx_tup_read as tuples_read,
sui.idx_tup_fetch as tuples_fetched,
pg_size_pretty(pg_relation_size(sui.indexrelid)) as index_size,
pg_relation_size(sui.indexrelid) as size_bytes,
sui.last_idx_scan,
(sui.idx_scan = 0 AND sui.last_idx_scan IS NULL) as potentially_new,
COALESCE(sut.n_live_tup, 0) as table_rows
FROM pg_stat_user_indexes sui
LEFT JOIN pg_stat_user_tables sut ON sui.relid = sut.relid
WHERE sui.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY sui.idx_scan ASC, pg_relation_size(sui.indexrelid) DESC
LIMIT 50
`);
const indexes = (indexResult.rows ?? []) as unknown as IndexRow[];
// Separate indexes by status with improved categorization
// Empty table indexes: 0 scans because table has no data
const emptyTableIndexes = indexes.filter((idx: IndexRow) =>
idx.index_scans === 0 && idx.table_rows === 0
);
// Potentially new: 0 scans, table has data, no scan history
const potentiallyNewIndexes = indexes.filter((idx: IndexRow) =>
idx.index_scans === 0 && idx.potentially_new && idx.table_rows > 0
);
// Truly unused: 0 scans, table has data, has scan history (was used before)
const trulyUnusedIndexes = indexes.filter((idx: IndexRow) =>
idx.index_scans === 0 &&
!idx.potentially_new &&
idx.table_rows > 0 &&
idx.size_bytes > 1024 * 1024 // > 1MB
);
const rarelyUsed = indexes.filter((idx: IndexRow) =>
idx.index_scans > 0 &&
idx.index_scans < 100 &&
idx.size_bytes > 10 * 1024 * 1024 // > 10MB
);
// Generate recommendations
const recommendations: IndexRecommendation[] = [];
// Include indexes on empty tables as info
for (const idx of emptyTableIndexes.slice(0, 3)) {
recommendations.push({
type: 'EMPTY_TABLE',
priority: 'INFO',
index: idx.schemaname + '.' + idx.indexname,
table: idx.tablename,
size: idx.index_size,
scans: idx.index_scans,
tableRows: idx.table_rows,
message: 'Index has 0 scans because table is empty. Will be used when data is inserted.'
});
}
// Include recently created indexes as info only
for (const idx of potentiallyNewIndexes.slice(0, 3)) {
recommendations.push({
type: 'RECENTLY_CREATED',
priority: 'LOW',
index: idx.schemaname + '.' + idx.indexname,
table: idx.tablename,
size: idx.index_size,
scans: idx.index_scans,
tableRows: idx.table_rows,
message: `Index has 0 scans on active table (${String(idx.table_rows)} rows). May be newly created or for rarely-run queries.`
});
}
for (const idx of trulyUnusedIndexes.slice(0, 5)) {
recommendations.push({
type: 'UNUSED_INDEX',
priority: 'HIGH',
index: idx.schemaname + '.' + idx.indexname,
table: idx.tablename,
size: idx.index_size,
scans: idx.index_scans,
action: 'DROP INDEX IF EXISTS ' + idx.schemaname + '.' + idx.indexname + ';',
benefit: 'Reclaim ' + idx.index_size + ' and reduce write overhead'
});
}
for (const idx of rarelyUsed.slice(0, 3)) {
recommendations.push({
type: 'RARELY_USED',
priority: 'MEDIUM',
index: idx.schemaname + '.' + idx.indexname,
table: idx.tablename,
size: idx.index_size,
scans: idx.index_scans,
action: '-- Review before dropping: ' + idx.schemaname + '.' + idx.indexname,
benefit: idx.index_scans.toString() + ' scans for ' + idx.index_size + ' index'
});
}
if (recommendations.length === 0) {
recommendations.push({
type: 'HEALTHY',
message: 'No obvious index optimization opportunities found'
});
}
return {
totalIndexes: indexes.length,
emptyTableIndexes: emptyTableIndexes.length,
potentiallyNewIndexes: potentiallyNewIndexes.length,
unusedIndexes: trulyUnusedIndexes.length,
rarelyUsedIndexes: rarelyUsed.length,
indexDetails: indexes.slice(0, 20),
recommendations,
summary: `Analyzed ${indexes.length.toString()} indexes. Found ${trulyUnusedIndexes.length.toString()} unused on active tables, ${emptyTableIndexes.length.toString()} on empty tables, ${potentiallyNewIndexes.length.toString()} potentially new, and ${rarelyUsed.length.toString()} rarely-used.`
};
}
};
}
