-> Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=4) (a
ctual time=87400.737..512954.539 rows=200000000 loops=1)
Total runtime: 1086336.466 ms
(3 rows)
postgres=# alter table alter_benchmark add test text;
ALTER TABLE
takes insignificant time (less than a second).
I feel so spoiled using PostgreSQL :-D
As I understand it PostgreSQL doesn't rewrite the table to change the column. It might to change the data type of a column. EXPLAIN ANALYZE doesn't work with ALTER TABLE because there is no query plan generated, so I have no idea how quickly the statement actually executed. All I know is it completed in under a second.
Re:Don't use MongoDB
by phaylon on Monday November 07, 2011 @11:46AM
(#3206988)
You could try `time psql < alter-statement.sql`. I know, it'd not really be useful as it measures lots of overhead. But if it's fast on that, it's fast during an active session.
postgres=CREATE TABLE alter_benchmark(id bigint);
CREATE TABLE
postgres=# explain analyze
postgres-# insert into alter_benchmark (id) select * from generate_series(1, 200000000);
postgres=# create temporary table alter_benchmark(id bigint); CREATE TABLE postgres=# explain analyze insert into alter_benchmark (id) select * from generate_series(1, 200000000); QUERY PLAN
-------------------------------------------------------------------------------- ---------------------------------------------------------
Insert (cost=0.00..12.50 rows=1000 width=4) (actual time=1082180.877..1082180. 877 rows=0 loops=1)
ctual time=87400.737..512954.539 rows=200000000 loops=1) Total runtime: 1086336.466 ms (3 rows)postgres=# alter table alter_benchmark add test text;
ALTER TABLE
takes insignificant time (less than a second).
I feel so spoiled using PostgreSQL :-D
As I understand it PostgreSQL doesn't rewrite the table to change the column. It might to change the data type of a column. EXPLAIN ANALYZE doesn't work with ALTER TABLE because there is no query plan generated, so I have no idea how quickly the statement actually executed. All I know is it completed in under a second.