PostgreSQL 인덱스 관리 스크립트

PostgreSQL 인덱스 관리 스크립트

---

1. PK(Unique) 인덱스를 가지고 있지 않은 테이블 확인

SELECT table_catalog, table_schema, table_name

FROM information_schema.tables

WHERE table_schema NOT IN ('information_schema', 'pg_catalog')

EXCEPT SELECT table_catalog, table_schema, table_name

FROM information_schema.table_constraints

WHERE constraint_type IN ('PRIMARY KEY', 'UNIQUE') 

AND table_schema NOT IN ('information_schema', 'pg_catalog');

2. 인덱스 중복 생성 확인

WITH index_info AS

(

SELECT 

pg_get_indexdef(indexrelid) AS index_def, 

indexrelid::regclass index_name, 

indrelid::regclass table_name, 

array_agg(attname) AS index_att

FROM pg_index i 

JOIN pg_attribute a 

ON i.indexrelid = a.attrelid

GROUP BY pg_get_indexdef(indexrelid), indrelid,  indexrelid

)

SELECT DISTINCT

  CASE WHEN a.index_name > b.index_name THEN a.index_def ELSE b.index_def END AS index_def,

  CASE WHEN a.index_name > b.index_name THEN a.index_name ELSE b.index_name END AS index_name,

  CASE WHEN a.index_name > b.index_name THEN b.index_def ELSE a.index_def END AS overlap_index_def,

  CASE WHEN a.index_name > b.index_name THEN b.index_def ELSE a.index_def END AS overlap_index_name,

  a.table_name

FROM index_info a 

INNER JOIN index_info b 

ON (

a.index_name != b.index_name 

AND a.table_name = b.table_name 

AND a.index_att && b.index_att 

);