문제 설명
Postgres에서 테이블의 모든 인덱스를 삭제하려면 어떻게 해야 합니까? (How can I drop all indexes of a table in Postgres?)
이 문제가 계속 발생합니다. 테스트를 수행하기 위해 삭제해야 하는 테이블에 약 20개의 인덱스가 있습니다. 테이블을 삭제해도 이 메타데이터가 모두 삭제되지 않습니다.
와일드 카드 drop index ix_table_* 또는 유용한 명령이 없는 것 같습니다. 작성할 수 있는 psql 주변에 bash 루프가 있는 것 같습니다. 더 나은 것이 있어야 합니다! 생각?
참조 솔루션
방법 1:
Assuming you only want to drop plain indexes:
DO
$do$
DECLARE
_sql text;
BEGIN
SELECT 'DROP INDEX ' || string_agg(indexrelid::regclass::text, ', ')
FROM pg_index i
LEFT JOIN pg_depend d ON d.objid = i.indexrelid
AND d.deptype = 'i'
WHERE i.indrelid = 'table_name'::regclass ‑‑ possibly schema‑qualified
AND d.objid IS NULL ‑‑ no internal dependency
INTO _sql;
IF _sql IS NOT NULL THEN ‑‑ only if index(es) found
EXECUTE _sql;
END IF;
END
$do$;
Does not touch indexes created as implementation detail of constraints (UNIQUE, PK, EXCLUDE).
The documentation:
DEPENDENCY_INTERNAL (i)The dependent object was created as part of creation of the referenced object, and is really just a part of its internal implementation.
You could wrap this in a function for repeated execution.
Related:
Related:
Aside: This is a misunderstanding:
Dropping the table doesn't drop all of this metadata.
Dropping a table always cascades to all indexes on the table.
방법 2:
This is how I remove all indexes from postgres, excluding all pkey.
CREATE OR REPLACE FUNCTION drop_all_indexes() RETURNS INTEGER AS $$
DECLARE
i RECORD;
BEGIN
FOR i IN
(SELECT relname FROM pg_class
‑‑ exclude all pkey, exclude system catalog which starts with 'pg_'
WHERE relkind = 'i' AND relname NOT LIKE '%_pkey%' AND relname NOT LIKE 'pg_%')
LOOP
‑‑ RAISE INFO 'DROPING INDEX: %', i.relname;
EXECUTE 'DROP INDEX ' || i.relname;
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
To execute:
SELECT drop_all_indexes();
Before actually executing 'DROP INDEX xxx', I would comment out the line 'EXECUTE ...' using '‑‑ ', and uncomment the 'RAISE INFO' line, run it with 'select func_name();' and double check I'm not dropping something I should not.
For our application, we have all schema statements including indexes creation in one file app.sql. Before this whole project goes to production, we want to clean up all historically created indexes, then recreate them using:
psql ‑f /path/to/app.sql
Hope this helps.
방법 3:
The query below drops all user indexes which are not related with any constraint (primary key, unique key)
SELECT
format('DROP INDEX %I.%I;', n.nspname, c_ind.relname)
FROM pg_index ind
JOIN pg_class c_ind ON c_ind.oid = ind.indexrelid
JOIN pg_namespace n ON n.oid = c_ind.relnamespace
LEFT JOIN pg_constraint cons ON cons.conindid = ind.indexrelid
WHERE
n.nspname NOT IN ('pg_catalog','information_schema') AND
n.nspname !~ '^pg_toast'::TEXT AND
cons.oid IS NULL
You can use \gexec meta‑command feature of psql to execute statement
(by Erin、Erwin Brandstetter、Emily、Sahap Asci)