BEGIN;
DROP INDEX OLD_IND;
ALTER INDEX NEW_INDEX RENAME TO OLD_INDEX;
COMMIT;
Experiment
gnb=# \d pgbench_accounts;
Table "public.pgbench_accounts"
Column | Type | Modifiers
---------------+---------------+-----------
aid | integer | not null
bid | integer |
abalance | integer |
filler | character(84) |
modified_date | date |
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
gnb=# create index accounts_ind on pgbench_accounts (abalance);
CREATE INDEX
gnb=# \d pgbench_accounts;
Table "public.pgbench_accounts"
Column | Type | Modifiers
---------------+---------------+-----------
aid | integer | not null
bid | integer |
abalance | integer |
filler | character(84) |
modified_date | date |
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
"accounts_ind" btree (abalance)
gnb=# select * from pg_indexes where tablename='pgbench_accounts';
schemaname | tablename | indexname | tablespace | indexdef
------------+------------------+-----------------------+------------+---------------------------------------------------------------------------------
public | pgbench_accounts | pgbench_accounts_pkey | | CREATE UNIQUE INDEX pgbench_accounts_pkey ON pgbench_accounts USING btree (aid)
public | pgbench_accounts | accounts_ind | | CREATE INDEX accounts_ind ON pgbench_accounts USING btree (abalance)
(2 rows)
create index concurrently accounts_ind_new on pgbench_accounts (abalance);
Begin;
drop index accounts_ind;
alter index accounts_ind_new rename to old_index;
commit;
The "concurrent....ly" keyword is to prevent the table to be locked up during the creation of the index. If there is this awesome feature, why not Postgresql just make it a default behavior ???
"There are several caveats to be aware of when using this option — see Building Indexes Concurrently."
ReplyDeletehttps://www.postgresql.org/docs/9.1/sql-createindex.html