PostgreSQL Example

示例 01

表名 articles

id 类型为整数, 不能为空, 主键, 唯一, 自增
datetime 类型为 TIMESTAMP, 不能为空
code 类型为 CHAR(6), 不能为空
source 类型为 VARCHAR(20), 不能为空
title 类型为 VARCHAR(100), 不能为空
url 类型为 TEXT, 不能为空

需求：

datetime, code, source, title, url 都需查询
code 和 url 组成唯一约束
source、title 和 url 经常需要使用 WHERE Like ‘%xxx%’ 搜索

根据 PostgreSQL 最佳实践，以及索引优化等，生成相关的建表语句以及建索引语句。

下面按 PostgreSQL 最佳实践 + 查询场景 + 索引可落地的思路，给你一套可直接用于生产的设计，并解释每一个关键决策。

一、字段类型与设计决策（先给结论）

字段	类型	原因
id	BIGINT GENERATED	自增主键，避免业务耦合
datetime	TIMESTAMP WITHOUT TIME ZONE	明确、可索引、通用
code	CHAR(6)	固定长度证券代码（合理）
source	VARCHAR(20)	来源长度有上限，LIKE 查询
title	VARCHAR(100)	标题长度有上限，LIKE 查询
url	TEXT	URL 长度不确定，LIKE 查询
唯一约束	(code, url)	防止重复文章

⚠️ LIKE ‘%xxx%’ 无法使用普通 B-Tree 索引，必须用 GIN + trigram

二、建表语句（推荐写法）

✅ PostgreSQL 10+ 推荐（IDENTITY）

CREATE TABLE articles (

    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    datetime TIMESTAMP NOT NULL,

    code CHAR(6) NOT NULL,

    source VARCHAR(20) NOT NULL,

    title VARCHAR(100) NOT NULL,

    url TEXT NOT NULL,

    CONSTRAINT uq_articles_code_url UNIQUE (code, url)

);

如果你更偏老版本，也可以用 BIGSERIAL，但 IDENTITY 是最佳实践

三、基础索引（必备）

1️⃣ datetime 查询 / 排序（非常常见）

CREATE INDEX idx_articles_datetime
ON articles (datetime DESC);

适用于：

WHERE datetime >= ...
ORDER BY datetime DESC

2️⃣ code 高频过滤

CREATE INDEX idx_articles_code
ON articles (code);

3️⃣ code + datetime 组合查询（推荐）

如果你经常这样查：

WHERE code = '000001'
ORDER BY datetime DESC

👉 复合索引比单列更优

CREATE INDEX idx_articles_code_datetime
ON articles (code, datetime DESC);

四、LIKE ‘%xxx%’ 查询的正确姿势（🔥重点）

❌ 普通索引无效

WHERE title LIKE '%xxx%'

B-Tree 完全用不上

五、启用 trigram 扩展（必须）

CREATE EXTENSION IF NOT EXISTS pg_trgm;

六、为 LIKE ‘%xxx%’ 建 GIN 索引（核心）

1️⃣ source 模糊搜索

CREATE INDEX idx_articles_source_trgm
ON articles
USING GIN (source gin_trgm_ops);

2️⃣ title 模糊搜索

CREATE INDEX idx_articles_title_trgm
ON articles
USING GIN (title gin_trgm_ops);

3️⃣ url 模糊搜索（可选，但你说“经常”）

CREATE INDEX idx_articles_url_trgm
ON articles
USING GIN (url gin_trgm_ops);

七、LIKE 查询将会真正走索引

EXPLAIN ANALYZE
SELECT *
FROM articles
WHERE title LIKE '%芯片%';

你将看到：

Bitmap Index Scan on idx_articles_title_trgm

🚀 性能提升 10～100 倍

八、如果经常三字段一起搜索（进阶）

例如：

WHERE source LIKE '%新浪%'
   OR title  LIKE '%芯片%'
   OR url    LIKE '%news%'

👉 可选方案：多列 GIN（权衡）

CREATE INDEX idx_articles_search_trgm
ON articles
USING GIN (
    source gin_trgm_ops,
    title  gin_trgm_ops,
    url    gin_trgm_ops
);

⚠️ 注意：

索引体积会明显变大
写入成本增加
如果查询字段经常一起出现 -> 值得

九、性能 & 运维最佳实践建议（非常重要）

1️⃣ CHAR(6) 查询记得 TRIM（如果来源不规范）

WHERE code = '000001'

确保插入时已标准化，否则 CHAR 会补空格

2️⃣ 定期维护（GIN 索引必做）

VACUUM (ANALYZE) articles;

高写入量系统建议：

autovacuum_vacuum_scale_factor = 0.05

3️⃣ 不要对 TEXT / VARCHAR 纠结性能

PostgreSQL 中：

TEXT 与 VARCHAR 性能完全一致

十、完整 SQL 汇总（可直接执行）

-- 导出数据
-- id 是自增主键，并无实际的意义，忽略
BEGIN;
SET LOCAL DateStyle = 'ISO, YMD';
\copy articles (datetime, code, source, title, url) TO '/tmp/articles.csv' WITH (FORMAT csv, HEADER true);
COMMIT;

-- 删除表
DROP TABLE IF EXISTS articles CASCADE;

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建表
CREATE TABLE articles (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    datetime TIMESTAMP NOT NULL,
    code CHAR(6) NOT NULL,
    source VARCHAR(20) NOT NULL,
    title VARCHAR(100) NOT NULL,
    url TEXT NOT NULL,
    CONSTRAINT uq_articles_code_url UNIQUE (code, url)
);

-- 提高统计精度
ALTER TABLE articles
ALTER COLUMN source SET STATISTICS 1000;

ALTER TABLE articles
ALTER COLUMN title SET STATISTICS 1000;

ALTER TABLE articles
ALTER COLUMN url SET STATISTICS 1000;

-- 创建索引

CREATE INDEX idx_articles_datetime
ON articles (datetime DESC);

CREATE INDEX idx_articles_code
ON articles (code);

CREATE INDEX idx_articles_code_datetime
ON articles (code, datetime DESC);

-- DROP INDEX idx_articles_source_trgm;

CREATE INDEX idx_articles_source_trgm
ON articles USING GIN (source gin_trgm_ops);

-- DROP INDEX idx_articles_title_trgm;

CREATE INDEX idx_articles_title_trgm
ON articles USING GIN (title gin_trgm_ops);

-- DROP INDEX idx_articles_url_trgm;

CREATE INDEX idx_articles_url_trgm
ON articles USING GIN (url gin_trgm_ops);

-- 导入数据
BEGIN;
SET LOCAL DateStyle = 'ISO, YMD';
\copy articles (datetime, code, source, title, url) FROM '/tmp/articles.csv' WITH (FORMAT csv, HEADER true);
COMMIT;

-- 检查序列
SELECT
    last_value,
    increment_by
FROM
    pg_sequences
WHERE
    schemaname = 'public'
    AND sequencename = 'articles_id_seq';

-- 回收
VACUUM articles;
VACUUM ANALYZE articles;

-- 分析表
ANALYZE articles;

url 使用 GIN 索引前后 EXPLAIN ANALYZE 对比

-- 统计行数

SELECT COUNT(*) FROM articles;

-- 10852

-- 索引前

EXPLAIN ANALYZE SELECT * FROM articles WHERE url LIKE '%weixin%';

-- Seq Scan on articles  (cost=0.00..411.45 rows=1050 width=168) (actual time=0.021..2.771 rows=1021 loops=1)
--   Filter: (url ~~ '%weixin%'::text)
--   Rows Removed by Filter: 9831
-- Planning Time: 0.108 ms
-- Execution Time: 2.828 ms

-- 索引后

EXPLAIN ANALYZE SELECT * FROM articles WHERE url LIKE '%weixin%';

-- Bitmap Heap Scan on articles  (cost=18.31..306.45 rows=1051 width=168) (actual time=0.302..1.095 rows=1021 loops=1)
--   Recheck Cond: (url ~~ '%weixin%'::text)
--   Heap Blocks: exact=233
--   ->  Bitmap Index Scan on idx_articles_url_trgm  (cost=0.00..18.05 rows=1051 width=0) (actual time=0.248..0.248 rows=1021 loops=1)
--         Index Cond: (url ~~ '%weixin%'::text)
-- Planning Time: 1.743 ms
-- Execution Time: 1.192 ms

以上输出显示了 PostgreSQL 对相同查询使用两种执行计划的对比，这里详细分析和总结优化要点。

一、查询目标

SELECT * FROM articles WHERE url LIKE '%weixin%';

查询条件是 LIKE ‘%xxx%’
PostgreSQL 需要匹配 任意位置的子串，普通 B-Tree 索引无法加速，需要 trigram（pg_trgm）索引。

二、执行计划对比

1️⃣ Seq Scan（顺序扫描）

Seq Scan on articles  (cost=0.00..411.45 rows=1050 width=168)
Filter: (url ~~ '%weixin%'::text)
Rows Removed by Filter: 9831
Execution Time: 2.828 ms

分析：

Seq Scan：逐行扫描整个表
Rows Removed by Filter: 9831 -> 表示表中大部分行不满足条件
Execution Time 2.828 ms -> 对于这个表量（~11k 行），还算快
优点：不依赖索引，开销低，适合小表或高选择性查询
缺点：表变大时成本线性增长

2️⃣ Bitmap Heap Scan + trgm index

Bitmap Heap Scan on articles  (cost=18.31..306.45 rows=1051 width=168)
Recheck Cond: (url ~~ '%weixin%'::text)
Heap Blocks: exact=233
->  Bitmap Index Scan on idx_articles_url_trgm
Index Cond: (url ~~ '%weixin%'::text)
Execution Time: 1.192 ms

分析：

使用了 pg_trgm 索引（idx_articles_url_trgm）
Bitmap Index Scan -> 先用索引找出可能匹配的行
Bitmap Heap Scan -> 再访问实际表行
Rows matched ≈ 1021 -> 和 Seq Scan 一致
Execution Time 1.192 ms -> 比 Seq Scan 快约 2x

三、结论

特性	Seq Scan	Bitmap Heap Scan + trgm
使用索引	❌	✅
适合场景	小表 / 查询高选择性	LIKE ‘%xxx%’ / 表大
扫描成本	O(n)	O(匹配行 + bitmap)
执行时间	较慢	快 2~3 倍

四、优化建议

保持 pg_trgm 索引

CREATE INDEX idx_articles_url_trgm ON articles USING gin (url gin_trgm_ops);

支持 %xxx% 模糊匹配
对大表效果明显

选择性考虑条件
- 如果匹配行很多，Bitmap Heap Scan 会访问大量行
- 可以结合其他过滤条件减少匹配量
避免常规 B-Tree 索引
- B-Tree 索引只支持 LIKE ‘xxx%’（前缀匹配）
- 对 %xxx% 无效
定期 VACUUM ANALYZE
- 保证查询规划器能正确估算行数，选择最佳计划

五、总结

对小表，Seq Scan 足够
对大表、LIKE ‘%xxx%’ 查询，pg_trgm + GIN/GIN_TRGM 索引显著提升性能
Bitmap Heap Scan + trgm 索引是最佳实践

示例 02

表名 telegraph

id 类型为整数, 不能为空, 主键, 唯一, 不自增
datetime 类型为 TIMESTAMP, 不能为空
code 类型为 CHAR(6), 不能为空
title 类型为 VARCHAR(100), 不能为空
content 类型为 TEXT, 不能为空

需求：

datetime, code, title 都需查询，content 不需要查询
title 经常需要使用 WHERE Like ‘%xxx%’ 搜索

实践：

-- 导出数据
BEGIN;
SET LOCAL DateStyle = 'ISO, YMD';
\copy telegraph TO '/tmp/telegraph.csv' WITH (FORMAT csv, HEADER true);
COMMIT;

-- 删除表
DROP TABLE IF EXISTS telegraph CASCADE;

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建表
CREATE TABLE telegraph (
    id BIGINT PRIMARY KEY,
    datetime TIMESTAMP NOT NULL,
    code CHAR(6) NOT NULL,
    title VARCHAR(100) NOT NULL,
    content TEXT NOT NULL
);

-- 提高统计精度
ALTER TABLE telegraph
ALTER COLUMN title SET STATISTICS 1000;

-- 创建索引

CREATE INDEX idx_telegraph_datetime
ON telegraph (datetime DESC);

CREATE INDEX idx_telegraph_code
ON telegraph (code);

CREATE INDEX idx_telegraph_code_datetime
ON telegraph (code, datetime DESC);

-- DROP INDEX idx_telegraph_title_trgm;

CREATE INDEX idx_telegraph_title_trgm
ON telegraph USING GIN (title gin_trgm_ops);

-- 导入数据
BEGIN;
SET LOCAL DateStyle = 'ISO, YMD';
\copy telegraph FROM '/tmp/telegraph.csv' WITH (FORMAT csv, HEADER true);
COMMIT;

-- 回收
VACUUM telegraph;
VACUUM ANALYZE telegraph;

-- 分析表
ANALYZE telegraph;