— 连接到数据库
$ psql -h 192.168.1.101 -p 5432 -U fgedu -d fgedudb

— 创建测试表
fgedudb=> CREATE TABLE fgedu_test_insert (
id serial PRIMARY KEY,
name varchar(50) NOT NULL,
value integer NOT NULL,
created_at timestamp DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE

— 单条INSERT
fgedudb=> INSERT INTO fgedu_test_insert (name, value) VALUES (‘test1’, 1);
INSERT 0 1

— 批量INSERT
fgedudb=> INSERT INTO fgedu_test_insert (name, value)
VALUES (‘test2’, 2), (‘test3’, 3), (‘test4’, 4), (‘test5’, 5), (‘test6’, 6);
INSERT 0 5

— 使用COPY命令批量导入
fgedudb=> \copy fgedu_test_insert (name, value) FROM ‘/tmp/test_data.csv’ DELIMITER ‘,’ CSV;
COPY 100000

— 查看导入结果
fgedudb=> SELECT count(*) FROM fgedu_test_insert;
count
——–
100006
(1 row)

— 创建索引
fgedudb=> CREATE INDEX idx_test_insert_name ON fgedu_test_insert(name);
CREATE INDEX

— 优化前：全表扫描
fgedudb=> EXPLAIN ANALYZE UPDATE fgedu_test_insert SET value = value + 1 WHERE name = ‘test1’;
QUERY PLAN
—————————————————————————————————————-
Update on fgedu_test_insert (cost=0.43..8.45 rows=1 width=44) (actual time=0.025..0.025 rows=0 loops=1)
-> Index Scan using idx_test_insert_name on fgedu_test_insert (cost=0.43..8.45 rows=1 width=44) (actual time=0.010..0.012 rows=1 loops=1)
Index Cond: (name = ‘test1’::character varying)
Planning Time: 0.112 ms
Execution Time: 0.045 ms
(5 rows)

— 批量UPDATE
fgedudb=> UPDATE fgedu_test_insert SET value = value + 1 WHERE id BETWEEN 1 AND 1000;
UPDATE 1000

— 带条件的UPDATE 风哥提示：
fgedudb=> UPDATE fgedu_test_insert SET value = 0 WHERE value < 0; UPDATE 0

— 删除单条记录
fgedudb=> DELETE FROM fgedu_test_insert WHERE id = 1; 学习交流加群风哥微信: itpux-com
DELETE 1

— 批量删除
fgedudb=> DELETE FROM fgedu_test_insert WHERE id BETWEEN 2 AND 1001;
DELETE 1000

— 截断表（快速删除所有数据）
fgedudb=> TRUNCATE TABLE fgedu_test_insert;
TRUNCATE TABLE

— 查看结果
fgedudb=> SELECT count(*) FROM fgedu_test_insert;
count
——-
0
(1 row)

— 重新插入测试数据
fgedudb=> INSERT INTO fgedu_test_insert (name, value)
SELECT ‘name’ || i, i
FROM generate_series(1, 100000) i;
INSERT 0 100000

— 优化前：全表扫描
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_test_insert WHERE value > 50000;
QUERY PLAN 学习交流加群风哥QQ113257174
—————————————————————————————————————-
Gather (cost=1000.00..1833.00 rows=50000 width=44) (actual time=0.158..29.345 rows=50000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on fgedu_test_insert (cost=0.00..1333.00 rows=20833 width=44) (actual time=0.032..15.678 rows=16667 loops=3)
Filter: (value > 50000)
Rows Removed by Filter: 33333
Planning Time: 0.104 ms
Execution Time: 32.672 ms
(7 rows)

— 创建索引
fgedudb=> CREATE INDEX idx_test_insert_value ON fgedu_test_insert(value);
CREATE INDEX

— 优化后：索引扫描
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_test_insert WHERE value > 50000;
QUERY PLAN
—————————————————————————————————————-
Bitmap Heap Scan on fgedu_test_insert (cost=432.00..7432.00 rows=50000 width=44) (actual time=0.234..45.678 rows=50000 loops=1)
Recheck Cond: (value > 50000)
Heap Blocks: exact=1234
-> Bitmap Index Scan on idx_test_insert_value (cost=0.00..419.50 rows=50000 width=0) (actual time=0.156..0.156 rows=50000 loops=1)
Index Cond: (value > 50000)
Planning Time: 0.123 ms
Execution Time: 52.345 ms
(6 rows)

— 使用索引覆盖查询
fgedudb=> EXPLAIN ANALYZE SELECT id, name FROM fgedu_test_insert WHERE value > 50000; 更多视频教程www.fgedu.net.cn
QUERY PLAN
—————————————————————————————————————-
Index Scan using idx_test_insert_value on fgedu_test_insert (cost=0.43..1234.56 rows=50000 width=18) (actual time=0.022..25.678 rows=50000 loops=1)
Index Cond: (value > 50000)
Planning Time: 0.107 ms
Execution Time: 28.345 ms
(4 rows)