cancel
Showing results for 
Search instead for 
Did you mean: 

DROP ALL INDEXES in a schema or database

SOLVED
Level 2 Adventurer

DROP ALL INDEXES in a schema or database

Hi,

 

I am looking to drop all indexes from the database or a schema. is anyone know how can I do that?

 

Thanks,

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Explorer

Re: DROP ALL INDEXES in a schema or database

If you are planning to drop all indexes in a schema or in a database, then you have to make sure following:

1. Ensure indexes of catalog schema like pg_catalog, information_schema (if you are using EPAS, then schema like sys, dbo) are excluded.

2. Ensure indexes of extensions are excluded.

3. Ensure indexes of all constraints are excluded. (i.e you should drop the constraints instead of dropping the indexes, which are imposing constraint)

Following is a query which will create DROP INDEX commands for the indexes in a user-defined schema and meets the above criteria.

SELECT  'DROP INDEX '||                  
    c.oid::regclass ||';'
FROM
    pg_catalog.pg_class c
    JOIN pg_catalog.pg_index i ON (
        c.oid = i.indexrelid )
    JOIN pg_class t ON (
        i.indrelid = t.oid )
    JOIN pg_namespace n ON (
        c.relnamespace = n.oid )
WHERE
    c.relkind = 'i'
    AND NOT EXISTS (
        SELECT
            1
        FROM
            pg_catalog.pg_constraint
        WHERE
            conindid = c.oid
            AND contype != 'f'
        LIMIT 1 )
    AND n.nspname = 'public' -- include your schemaname

    AND t.relkind IN (
        'r' :: "char",
        'm' :: "char",
        'p' :: "char")
    AND EXISTS (
        SELECT
            1
        FROM
            pg_catalog.pg_depend d
        WHERE
            d.objid = t.oid
            AND d.classid = 'pg_catalog.pg_class' ::REGCLASS:: OID
            AND d.objsubid = 0
            AND d.deptype = 'e'
        LIMIT 1 );

 

2 REPLIES
EDB Team Member

Re: DROP ALL INDEXES in a schema or database

Hi Sunthar,

 

Hope you are doing good.

 

There is no pre-defined function which deletes all the indexes available on a schema or database. 
You need to write customised PL/SQL function to achieve this. 
However, you can go through the following link which describes options available for dropping an index.
https://www.postgresql.org/docs/9.6/static/sql-dropindex.html

 

Thank You,
Swagata

Highlighted
Explorer

Re: DROP ALL INDEXES in a schema or database

If you are planning to drop all indexes in a schema or in a database, then you have to make sure following:

1. Ensure indexes of catalog schema like pg_catalog, information_schema (if you are using EPAS, then schema like sys, dbo) are excluded.

2. Ensure indexes of extensions are excluded.

3. Ensure indexes of all constraints are excluded. (i.e you should drop the constraints instead of dropping the indexes, which are imposing constraint)

Following is a query which will create DROP INDEX commands for the indexes in a user-defined schema and meets the above criteria.

SELECT  'DROP INDEX '||                  
    c.oid::regclass ||';'
FROM
    pg_catalog.pg_class c
    JOIN pg_catalog.pg_index i ON (
        c.oid = i.indexrelid )
    JOIN pg_class t ON (
        i.indrelid = t.oid )
    JOIN pg_namespace n ON (
        c.relnamespace = n.oid )
WHERE
    c.relkind = 'i'
    AND NOT EXISTS (
        SELECT
            1
        FROM
            pg_catalog.pg_constraint
        WHERE
            conindid = c.oid
            AND contype != 'f'
        LIMIT 1 )
    AND n.nspname = 'public' -- include your schemaname

    AND t.relkind IN (
        'r' :: "char",
        'm' :: "char",
        'p' :: "char")
    AND EXISTS (
        SELECT
            1
        FROM
            pg_catalog.pg_depend d
        WHERE
            d.objid = t.oid
            AND d.classid = 'pg_catalog.pg_class' ::REGCLASS:: OID
            AND d.objsubid = 0
            AND d.deptype = 'e'
        LIMIT 1 );