Copy indexes from one table to another

Question

I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm finished loading however I need to re-apply those indexes to the newly populated table. These indexes are created outside of the scope of these ETL jobs so I would rather not have to hardcode the CREATE INDEX calls if I don't have to.

Is it possible to "transfer" or "copy" a set of indexes from one table to another?

score 10 · Answer 1 · answered Nov 02 '15 at 20:26

You can retrieve the complete DDL statement for each index from the Postgres system catalogs.

The following will retrieve all CREATE index statements for a single table:

select pg_get_indexdef(idx.oid)||';'
from pg_index ind
  join pg_class idx on idx.oid = ind.indexrelid
  join pg_class tbl on tbl.oid = ind.indrelid
  left join pg_namespace ns on ns.oid = tbl.relnamespace
where tbl.relname = 'your_table_name'
   and ns.nspname = 'your_table_schema';

You can spool the output into a script and run it after you have copied the data and swapped the table names.

thanks! what would do the same for primary key and foreign keys contraints? — brauliobo, Jun 15 '16 at 13:50

score 5 · Answer 2 · edited Jun 15 '20 at 09:05

Basics

To get all index definitions you can use the system catalog information function pg_get_indexdef(index_oid) like @a_horse already provided.

The query can be considerably simpler, though, using a regclass parameter. For a table called tbl in the public schema:

SELECT pg_get_indexdef(indexrelid) || ';' AS idx
FROM   pg_index
WHERE  indrelid = 'public.tbl'::regclass;  -- optionally schema-qualified

This includes all indexes: PK, partial, functional, unique, with special operator classes, etc.

Details for regclass:

SQL injection in Postgres functions vs prepared queries

Prepare DDL statements

You cannot work with this, yet. You have to replace the old table name with the new one - and you don't want to replace false positives in the string (like a column name that matches the table name):

If you are working with the default naming convention of Postgres, index definitions look like this:

CREATE INDEX tbl_tbl_id_idx ON tbl USING btree (tbl_id);
CREATE INDEX tbl_people_gin_idx ON tbl
USING gin (((data -> 'people'::text)) jsonb_path_ops);
CREATE INDEX tbl_comecol_nonull_idx ON tbl
USING btree (somecol) WHERE (cutblade IS NOT NULL);

I bolded every occurrence of the original table name. Note the three false positives (part of column name or index name) which we do not want to replace.

This query should work flawlessly - but verify that against your actual indexes yourself!

SELECT regexp_replace(regexp_replace(
          pg_get_indexdef(indexrelid)
        , 'tbl', 'tbl1')
        , ' ON tbl ', ' ON tbl1 ')
    || ';' AS idx
FROM   pg_index
WHERE  indrelid = 'public.tbl'::regclass;

The 1st replace() only replaces the first match in the name (unless instructed otherwise). The 2nd regexp_preplace() is more specific and only replaces the actual table name.

For the given examples you get:

CREATE INDEX tbl1_tbl_id_idx ON tbl1 USING btree (tbl_id);
CREATE INDEX tbl1_people_gin_idx ON tbl1
USING gin (((data -> 'people'::text)) jsonb_path_ops);
CREATE INDEX tbl1_somecol_nonull_idx ON tbl1
USING btree (somecol) WHERE (cutblade IS NOT NULL);

But we have not yet considered non-standard table names, schemas or the search_path setting. All of that is built into the function below.

Full automation

If you are confident in your naming scheme you can fully automate:

CREATE OR REPLACE FUNCTION f_copy_idx(_tbl text, _tbl1 text
                                    , _sch text = 'public', _sch1 text = 'public')
  RETURNS void AS
$func$
DECLARE
   _full_tbl  text := format('%I.%I', _sch, _tbl);
   _full_tbl1 text := format('%I.%I', _sch1, _tbl1);
BEGIN
   -- RAISE NOTICE '%', -- for testing
   EXECUTE
   (SELECT string_agg(
              regexp_replace(regexp_replace(
                 pg_get_indexdef(indexrelid)
               , _tbl,  _tbl1)
               , ' ON ' || _full_tbl || ' ',  ' ON ' || _full_tbl1 || ' ')
            , '; ') AS idx
    FROM   pg_index
    WHERE  indrelid = _full_tbl::regclass);
END
$func$ LANGUAGE plpgsql SET search_path = '';

This is also prepared for non-standard table names that need double-quoting and differing schemas.

To force schema-qualified table names I reset the search_path inside the function (SET search_path = ''). This way I get reliable results that do not depend on the setting.

I added 'public' as default value for the schema parameters _sch and _sch1. So if both tables reside in the public schema, you can simple ignore the schema parameters.

Call:

SELECT f_copy_idx('mytbl', 'newtbl');  -- all in schema public

Or for table names that require double-quoting and different schemas:

SELECT f_copy_idx('old_TBL', 'table', 'public', 'New_SCHEmA');

SQL Fiddle demonstrating the function in use.

score 2 · Answer 3 · answered Nov 02 '15 at 20:21

While you can't "copy" them you can use the database meta-data to dynamically generate the DDL to create them.

SELECT 'CREATE ' 
            || CASE
                WHEN i.indisunique THEN 'UNIQUE '
                ELSE ''
                END
            || 'INDEX '
            || nr.nspname
            || '.'
            || c2.relname
            || '_swap ON '
            || nr.nspname
            || '.'
            || c.relname
            || '_swap ( '
            || split_part ( split_part ( pg_catalog.pg_get_indexdef ( i.indexrelid, 0, true ), '(', 2 ), ')', 1 )
            || ' ) '
    FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_index i
        ON ( c.oid = i.indrelid )
    JOIN pg_catalog.pg_class c2
        ON ( i.indexrelid = c2.oid )
    JOIN pg_namespace nr
        ON ( nr.oid = c.relnamespace )
    WHERE nr.nspname = 'schema for table1'
        AND c.relname = 'table1' ;

score 0 · Answer 4 · answered Nov 02 '15 at 20:08

Kyle,

I'm afraid that, due to the storage structure of PostgreSQL, a simple copying of indexes is not possible. PostgreSQL uses a storage method called heap-structured tables, where the insertion of data into the table has no guaranteed ordering or position.

If PostgreSQL were to copy a complete table from one table record to another, including all versions of every row, information about deleted rows, etc., then I imagine that one would be able to copy indexes as well.

In the case of how PostgreSQL currently operates, copying over data from one table to another replica table only guarantees logical consistency: that is, every row present in the original table will be present in the new table, and the data will be a row by match at the row level only.

In terms of physical consistency, there is no guarantee that the way in which the data is physical stored on disk in the original table will match the physical storage of the data in the replica table.

The reason this is important is because an index is essentially a method for mapping some information about the data in the table to the CTID of the row which contains the data.

Long story short: other than some creative hacking, I believe the conventional wisdom is that you must rebuild these indexes on the new table. Sorry I don't have better news.

He doesn't want to copy the contents of the index, just the definition of the index. — jjanes, Nov 02 '15 at 21:22

Copy indexes from one table to another

4 Answers4

Basics

Prepare DDL statements

Full automation