Как я могу удалить все индексы таблицы в Postgres?

У меня проблема: у меня есть 20 индексов на таблице, которые мне нужно отбросить, чтобы провести тестирование. Отбрасывание таблицы не отбрасывает все эти метаданные.

Кажется, что нет подстановочного символа drop index ix_table_* или любой полезной команды. Кажется, есть несколько bash циклов вокруг psql, которые вы можете написать.
Там должно быть что-то лучше! Мысли?

Ответы

Ответ 1

Предполагая, что вы хотите удалить только простые индексы:

DO
$$BEGIN
   EXECUTE (
   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 = 'your_table_name_here'::regclass  -- possibly schema-qualified
   AND    d.objid IS NULL                                -- no internal dependency
   );
END$$;

Не затрагивает индексы, созданные как детали реализации ограничений (UNIQUE, PK, EXCLUDE).
Документация:

DEPENDENCY_INTERNAL (i)
Зависимый объект был создан как часть создания ссылочной объект, и на самом деле является лишь частью его внутренней реализации.

Вы можете заключить это в функцию для повторного выполнения.
Связанный:

Имя таблицы в качестве параметра функции PostgreSQL

В сторону: это недоразумение:

Удаление таблицы не удаляет все эти метаданные.

Отбрасывание таблицы всегда происходит каскадно по всем индексам в таблице.

Ответ 2

Вот как я удаляю все индексы из postgres, исключая все 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;

Для выполнения:

SELECT drop_all_indexes();

Прежде чем выполнять "DROP INDEX xxx", я бы прокомментировал строку "EXECUTE..." с помощью "-" и раскомментировал строку "RAISE INFO", запустил ее с помощью "select func_name(); и дважды проверяю, что я не отбрасываю то, что не должен.

Для нашего приложения мы имеем все инструкции схемы, включая создание индексов в одном файле app.sql. Перед тем, как весь этот проект идет на производство, мы хотим очистить все исторически созданные индексы, а затем воссоздать их, используя:

psql -f /path/to/app.sql

Надеюсь, что это поможет.

Ответ 3

Приведенный ниже запрос отбрасывает все пользовательские индексы, которые не связаны с каким-либо ограничением (первичный ключ, уникальный ключ)

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

Вы можете использовать функцию мета-команды \gexec в psql для выполнения оператора