数据压缩60%让“PostgreSQL” SQL运行更快，这不科学呀？

-- 清理旧表
DROP TABLE IF EXISTS table3;
DROP TABLE IF EXISTS table2;
DROP TABLE IF EXISTS table1;-- 创建表结构
CREATE TABLE table1 (id SERIAL PRIMARY KEY,name TEXT,value INT
);CREATE TABLE table2 (id SERIAL PRIMARY KEY,table1_id INT REFERENCES table1(id),description TEXT
);CREATE TABLE table3 (id SERIAL PRIMARY KEY,table2_id INT REFERENCES table2(id),tag TEXT
);

DO $$
DECLAREi INT := 1;start_time TIMESTAMP;end_time TIMESTAMP;
BEGINRAISE NOTICE '开始插入 table1';start_time := clock_timestamp();WHILE i <= 1000000 LOOPINSERT INTO table1(name, value) VALUES ('name_' || i, i);i := i + 1;END LOOP;end_time := clock_timestamp();RAISE NOTICE 'table1 插入耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);i := 1;RAISE NOTICE '开始插入 table2';start_time := clock_timestamp();WHILE i <= 1000000 LOOPINSERT INTO table2(table1_id, description) VALUES ((random()*999999 + 1)::INT, 'desc_' || i);i := i + 1;END LOOP;end_time := clock_timestamp();RAISE NOTICE 'table2 插入耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);i := 1;RAISE NOTICE '开始插入 table3';start_time := clock_timestamp();WHILE i <= 1000000 LOOPINSERT INTO table3(table2_id, tag) VALUES ((random()*999999 + 1)::INT, 'tag_' || i);i := i + 1;END LOOP;end_time := clock_timestamp();RAISE NOTICE 'table3 插入耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);
END
$$;

DO $$
DECLAREi INT := 1;start_time TIMESTAMP;end_time TIMESTAMP;
BEGINRAISE NOTICE '开始更新 table1';start_time := clock_timestamp();WHILE i <= 100000 LOOPUPDATE table1 SET value = value + 1 WHERE id = (random()*999999 + 1)::INT;i := i + 1;END LOOP;end_time := clock_timestamp();RAISE NOTICE 'table1 更新耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);i := 1;RAISE NOTICE '开始更新 table2';start_time := clock_timestamp();WHILE i <= 100000 LOOPUPDATE table2 SET description = description || '_u' WHERE id = (random()*999999 + 1)::INT;i := i + 1;END LOOP;end_time := clock_timestamp();RAISE NOTICE 'table2 更新耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);i := 1;RAISE NOTICE '开始更新 table3';start_time := clock_timestamp();WHILE i <= 100000 LOOPUPDATE table3 SET tag = tag || '_v' WHERE id = (random()*999999 + 1)::INT;i := i + 1;END LOOP;end_time := clock_timestamp();RAISE NOTICE 'table3 更新耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);
END
$$;

DO $$
DECLAREi INT := 1;start_time TIMESTAMP;end_time TIMESTAMP;dummy TEXT;
BEGINRAISE NOTICE '开始单表查询 table1';start_time := clock_timestamp();WHILE i <= 10000 LOOPSELECT name INTO dummy FROM table1 WHERE id = (random()*999999 + 1)::INT;i := i + 1;END LOOP;end_time := clock_timestamp();RAISE NOTICE 'table1 单表查询耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);i := 1;RAISE NOTICE '开始单表查询 table2';start_time := clock_timestamp();WHILE i <= 10000 LOOPSELECT description INTO dummy FROM table2 WHERE id = (random()*999999 + 1)::INT;i := i + 1;END LOOP;end_time := clock_timestamp();RAISE NOTICE 'table2 单表查询耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);i := 1;RAISE NOTICE '开始单表查询 table3';start_time := clock_timestamp();WHILE i <= 10000 LOOPSELECT tag INTO dummy FROM table3 WHERE id = (random()*999999 + 1)::INT;i := i + 1;END LOOP;end_time := clock_timestamp();RAISE NOTICE 'table3 单表查询耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);i := 1;RAISE NOTICE '开始多表联查';start_time := clock_timestamp();WHILE i <= 10000 LOOPSELECT t1.name, t2.description, t3.tagINTO dummy, dummy, dummyFROM table1 t1JOIN table2 t2 ON t1.id = t2.table1_idJOIN table3 t3 ON t2.id = t3.table2_idWHERE t1.id = (random()*999999 + 1)::INTLIMIT 1;i := i + 1;END LOOP;end_time := clock_timestamp();RAISE NOTICE '多表联查耗时: % 秒', EXTRACT(SECOND FROM end_time - start_time);
END
$$;