Re: How to do faster DML
От | veem v |
---|---|
Тема | Re: How to do faster DML |
Дата | |
Msg-id | CAB+=1TWZQ6gQjRJzysMHw6YUZw150gvxOAZ3KnK1i0pLF6SaOg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: How to do faster DML (Adrian Klaver <adrian.klaver@aklaver.com>) |
Ответы |
Re: How to do faster DML
Re: How to do faster DML |
Список | pgsql-general |
It depends:
https://www.postgresql.org/docs/current/sql-altertable.html
"Adding a column with a volatile
DEFAULT
or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if theUSING
clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed. However, indexes must always be rebuilt unless the system can verify that the new index would be logically equivalent to the existing one. For example, if the collation for a column has been changed, an index rebuild is always required because the new sort order might be different. However, in the absence of a collation change, a column can be changed fromtext
tovarchar
(or vice versa) without rebuilding the indexes because these data types sort identically. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space."
create table int_test(int_fld integer);
insert into int_test select * from generate_series(1, 10000, 1);
select ctid, int_fld from int_test ;
ctid | int_fld
----------+---------
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,5) | 5
(0,6) | 6
(0,7) | 7
(0,8) | 8
(0,9) | 9
(0,10) | 10
alter table int_test alter column int_fld set data type bigint;
select ctid, int_fld from int_test ;
ctid | int_fld
----------+---------
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,5) | 5
(0,6) | 6
(0,7) | 7
(0,8) | 8
(0,9) | 9
(0,10) | 10update int_test set int_fld = int_fld;
select ctid, int_fld from int_test order by int_fld;
(63,1) | 1
(63,2) | 2
(63,3) | 3
(63,4) | 4
(63,5) | 5
(63,6) | 6
(63,7) | 7
(63,8) | 8
(63,9) | 9
(63,10) | 10
Where ctid is:
https://www.postgresql.org/docs/current/ddl-system-columns.html
В списке pgsql-general по дате отправления:
Предыдущее
От: "David G. Johnston"Дата:
Сообщение: Re: Accessing parameters of a prepared query inside an FDW