francs=> create table test_1(id int4 primarykey, name character varying(64),create_time timestamp without time zone); NOTICE: CREATE TABLE / PRIMARYKEY will create implicit index"test_1_pkey"for table "test_1" CREATE TABLE francs=> insertinto test_1 select generate_series(1,10000000),generate_series(1,10000000)||'_francs',clock_timestamp(); INSERT010000000 francs=> select * From test_1 limit3; id | name | create_time ----+----------+---------------------------- 1 | 1_francs | 2013-04-27 16:28:11.465584 2 | 2_francs | 2013-04-27 16:28:11.465928 3 | 3_francs | 2013-04-27 16:28:11.465943 (3 rows)
备注：创建一张 1 千万数据的表做测试。
测试一 设置 maintenance_work_mem 为 1MB
1 2 3 4 5 6 7 8 9 10 11 12 13 14
francs=> show maintenance_work_mem; maintenance_work_mem ---------------------- 256MB (1 row) francs=> set maintenance_work_mem='1MB'; SET francs=> show maintenance_work_mem; maintenance_work_mem ---------------------- 1MB (1 row)
创建索引
1 2 3 4 5 6 7
francs=> create index concurrently idx_test_1_name on test_1 using btree (name); CREATE INDEX Time: 72676.986 ms francs=> create index concurrently idx_test_1_ctime on test_1 using btree (create_time); CREATE INDEX Time: 40593.181 ms
测试二 设置 maintenance_work_mem 为 10 MB
1 2 3 4 5 6 7 8 9 10 11 12 13 14
francs=> show maintenance_work_mem ; maintenance_work_mem ---------------------- 1MB (1 row) francs=> set maintenance_work_mem ='10MB'; SET francs=> show maintenance_work_mem ; maintenance_work_mem ---------------------- 10MB (1 row)
创建索引（先删除之前的两个索引）
1 2 3 4 5 6
francs=> create index concurrently idx_test_1_name on test_1 using btree (name); Time: 83767.538 ms francs=> create index concurrently idx_test_1_ctime on test_1 using btree (create_time); CREATE INDEX Time: 47679.868 ms
测试三 设置 maintenance_work_mem 为 1 GB
1 2 3 4 5 6 7 8 9 10 11 12 13 14
francs=> show maintenance_work_mem ; maintenance_work_mem ---------------------- 10MB (1 row) francs=> set maintenance_work_mem ='1GB'; SET francs=> show maintenance_work_mem ; maintenance_work_mem ---------------------- 1GB (1 row)
创建索引（先删除之前的两个索引）
1 2 3 4 5 6 7
francs=> create index concurrently idx_test_1_name on test_1 using btree (name); CREATE INDEX Time: 47657.969 ms francs=> create index concurrently idx_test_1_ctime on test_1 using btree (create_time); CREATE INDEX Time: 18953.915 ms
测试四 设置 maintenance_work_mem 为 2 GB
1 2 3 4 5 6 7 8 9 10 11
francs=> show maintenance_work_mem; maintenance_work_mem ---------------------- 1GB (1 row) francs=> show maintenance_work_mem; maintenance_work_mem ---------------------- 2047MB (1 row)
创建索引（先删除之前的两个索引）
francs=> create index concurrently idx_test_1_name on test_1 using btree (name);
CREATE INDEX
Time: 50843.263 ms
francs=> create index concurrently idx_test_1_ctime on test_1 using btree (create_time);
CREATE INDEX
Time: 13304.179 ms
