kingbase数据库查看指定数据表的索引使用情况

查询检查执行sql情况，是否用上索引

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
select * from table where 1=1

查询获取指定表的运行进程情况

SELECT 
    l.locktype,
    l.relation::regclass AS table_name,
    l.mode,
    l.pid,
    a.usename,
    a.application_name,
    a.query,
    a.state,
    a.query_start
FROM sys_locks l
JOIN sys_stat_activity a ON l.pid = a.pid
WHERE l.relation = 'table'::regclass
   OR l.relation::regclass::text LIKE 'table%';

查询表结构 索引定义

-- 查看表结构
SELECT column_name, data_type, character_maximum_length, is_nullable
FROM information_schema.columns 
WHERE table_name = 'table' AND column_name = 'xx_idno';

-- 查看索引定义
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'table' AND indexname LIKE '%xx_idno%';

查询索引使用情况

SELECT 
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan as '索引使用情况', --0表示未使用
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE relname = 'table';

删除索引，创建默认索引

--删除索引
DROP INDEX IF EXISTS 索引名称;

--创建默认索引
CREATE INDEX 新索引名称 ON cw_grant (cw_idno);