How can I drop all indexes of a table in Postgres?

Question

I keep having this problem: I have like 20 indexes on a table that I need to drop in order to do testing. Dropping the table doesn't drop all of this metadata.

There doesn't seem to be a wildcard drop index ix_table_* or any useful command. There seem to be some bash loops around psql you can write.
There must be something better! Thoughts?

When you say "all indexes", do you mean to include constraints (`UNIQUE`, `PK`, `EXCLUDE`) that are implemented by creating an index implicitly? — Erwin Brandstetter, Dec 01 '15 at 02:15

Erwin Brandstetter · Answer 1 · 2021-07-19T10:46:21.807

25

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:

Table name as a PostgreSQL function parameter

DROP FUNCTION without knowing the number/type of parameters?

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.

edited Jul 19 '21 at 10:46

answered Dec 01 '15 at 02:46

Erwin Brandstetter

605,456
145
1,078
1,228

Do you have any ideas why dropping the table would not cascade to the indexes? Because it doesn't. The tables & indexes were generated by me playing around with sql alchemy declarative base and mixin classes -- something that is a bit of a black box to me still. – user Dec 01 '15 at 05:06
@Erin: Are you sure, there isn't a second table of the same name in a different schema? – Erwin Brandstetter Dec 01 '15 at 05:37
Took a while to understand that the input to this query is `'tbl'` – Benjamin Crouzier Oct 13 '16 at 14:58
1

@BenjaminCrouzier: I changed to `'your_table_name_here'` to make it clearer. – Erwin Brandstetter Oct 20 '16 at 03:31
In order to clean indexes only from user's tables, use this condition: `where not indrelid::regclass::varchar like 'pg_%'` – Andremoniy Oct 21 '16 at 09:30
@Andremoniy: While providing a user's table name, that's not necessary. Maybe as safety measure for a general-purpose function. But `DROP INDEX` is only allowed for the owner of the table (or superusers) anyway. – Erwin Brandstetter Oct 21 '16 at 19:08
Be careful, this SQL fails if there is no index on the named table. – Neil Jul 18 '21 at 14:31
@Neil: Consider the update: not any more. – Erwin Brandstetter Jul 19 '21 at 10:47

score 11 · Answer 2 · answered Sep 04 '17 at 03:03

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.

This solution works perfectly for me. Thanks a lot! What I need to do is just change the select query a bit to fulfill my requirement. The idea of RAISE INFO saved me a lot of time. — LingYan Meng, Sep 12 '18 at 03:30

score 6 · Answer 3 · answered Sep 21 '19 at 09:01

6

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

answered Sep 21 '19 at 09:01

Sahap Asci

773
7
10

ps. Here's something similar for dropping constraints, should it help: `SELECT format ('ALTER TABLE %I.%I DROP CONSTRAINT %I;', nsp.nspname, rel.relname, con.conname) FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE nsp.nspname = 'cam' order by con.conname -- hacky way to ensure FKs come before PKs` – JohnLBevan Dec 01 '20 at 15:46

okharch · Answer 4 · 2022-07-22T12:19:50.187

For the sake of the KISS, I would consider the approach mentioned in the official implementation https://www.postgresql.org/docs/7.2/sql-altertable.html#:~:text=For%20example%2C%20to%20drop%20all%20constraints%20on%20a%20table

-- store table structure 
-- into temp table 
create temporary table t$ as 
select * from table_name limit 0;
-- drop the table, 
-- 'cascade' ensures it will not 
-- complain about any dependencies
drop table table_name cascade;
-- recreate the original table
create table table_name as 
select * from t$;
-- drop temporary table
drop table t$;

How can I drop all indexes of a table in Postgres?

4 Answers4

Linked