1
-- 预订冲突检测：查找与目标时间段重叠的预订
2
SELECT * FROM room_bookings
3
WHERE stay_period && '[2026-04-10, 2026-04-15]'::daterange;

1
-- 查找最近的 10 家咖啡店
2
SELECT name, ST_Distance(geom, ST_MakePoint(116.4, 39.9)::geography) AS dist
3
FROM cafes
4
ORDER BY geom <-> ST_MakePoint(116.4, 39.9)::geography
5
LIMIT 10;

1
-- 中文全文搜索需要 zhparser 或 jieba 等分词扩展
2
-- zhparser 只注册名为 zhparser 的 parser，还需自己建文本搜索配置（PostgreSQL 内置只有 english/german/simple 等配置，没有 chinese）
3
CREATE EXTENSION zhparser;
4
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
5
-- 配置 token 到词典的映射，否则分出的词素不进索引
6
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e,l WITH simple;
7

8
CREATE INDEX idx_posts_content ON posts USING gin (to_tsvector('chinese', content));
9

10
-- 查询：包含"索引"和"优化"的文章
11
SELECT * FROM posts
12
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', '索引 & 优化')
13
ORDER BY ts_rank(to_tsvector('chinese', content), to_tsquery('chinese', '索引 & 优化')) DESC;

1
-- 订单表有 1 亿行，其中"待支付"状态只有 10 万行
2
-- 建全表索引浪费空间，建部分索引只索引待支付行
3
CREATE INDEX idx_orders_pending ON orders (created_at)
4
WHERE status = 'pending';
5

6
-- 查询时会自动使用部分索引
7
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-04-01';

1
-- 大小写不敏感的用户名查询
2
CREATE INDEX idx_users_lower_name ON users (lower(username));
3

4
-- 查询时会使用表达式索引
5
SELECT * FROM users WHERE lower(username) = 'alice';
6

7
-- JSONB 路径索引
8
CREATE INDEX idx_events_type ON events ((data->>'type'));
9

10
-- 查询 JSONB 路径
11
SELECT * FROM events WHERE data->>'type' = 'login';

1
-- 顺序扫描代价 = 顺序读页数 × seq_page_cost + 行数 × cpu_tuple_cost
2
-- 假设表有 10000 页、500000 行
3
-- Seq Scan Cost = 10000 × 1.0 + 500000 × 0.01 = 15000
4

5
-- 索引扫描代价 = 随机读页数 × random_page_cost + 索引条目数 × cpu_index_tuple_cost
6
--              + 堆表行数 × cpu_tuple_cost
7
-- 假设 B-tree 深度 3、返回 100 行
8
-- Index Scan Cost = 3 × 4.0 + 100 × 0.005 + 100 × 0.01 = 13.5

1
-- SSD 环境下调低随机读代价
2
ALTER SYSTEM SET random_page_cost = 1.1;
3
SELECT pg_reload_conf();

1
-- 强制使用特定 Join 方式（仅调试用）
2
SET enable_nestloop = off;
3
SET enable_hashjoin = off;
4
-- 此时优化器只能选 Merge Join
5

6
-- 恢复默认
7
RESET enable_nestloop;
8
RESET enable_hashjoin;

1
-- 基本执行计划
2
EXPLAIN SELECT * FROM users WHERE city = 'Beijing';
3

4
-- 带实际执行时间
5
EXPLAIN ANALYZE SELECT * FROM users WHERE city = 'Beijing';
6

7
-- 带 I/O 统计（最常用）
8
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE city = 'Beijing';
9

10
-- 示例输出：
11
--  QUERY PLAN
12
--  ---------------------------------------------------------------
13
--  Index Scan using idx_users_city on users
14
--    (cost=0.29..8.31 rows=1 width=72)
15
--    (actual time=0.015..0.016 rows=1 loops=1)
16
--    Index Cond: (city = 'Beijing'::text)
17
--    Buffers: shared hit=4
18
--  Planning Time: 0.089 ms
19
--  Execution Time: 0.032 ms

1
-- 查看各索引的使用情况
2
SELECT schemaname, relname, indexrelname,
3
       idx_scan AS scans,
4
       idx_tup_read AS tuples_read,
5
       idx_tup_fetch AS tuples_fetched,
6
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
7
FROM pg_stat_user_indexes
8
ORDER BY idx_scan ASC;
9

10
-- 示例输出：
11
--  relname  |     indexrelname      | scans | tuples_read | size
12
-- ----------+----------------------+-------+-------------+------
13
--  orders   | idx_orders_status    |     0 |           0 | 45 MB  <- 从未使用
14
--  orders   | idx_orders_created   | 15234 |     1200000 | 50 MB

1
-- 删除从未使用的索引（先确认确实不需要）
2
DROP INDEX CONCURRENTLY idx_orders_status;

1
-- 查看索引大小和扫描次数
2
SELECT indexrelname,
3
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
4
       idx_scan
5
FROM pg_stat_user_indexes
6
WHERE relname = 'orders';
7

8
-- 如果索引大小远超预期（例如 B-tree 深度异常、叶子页稀疏），考虑 REINDEX

1
-- 并发重建单个索引（推荐）
2
REINDEX INDEX CONCURRENTLY idx_orders_created;
3

4
-- 并发重建某表所有索引
5
REINDEX TABLE CONCURRENTLY orders;

1
-- 错误：JSONB 查询用 B-tree，不生效
2
CREATE INDEX idx_events_data_btree ON events (data);  -- B-tree 无法索引 JSONB 内部
3
SELECT * FROM events WHERE data @> '{"type": "login"}'::jsonb;  -- 全表扫描
4

5
-- 正确：JSONB 查询用 GIN
6
CREATE INDEX idx_events_data_gin ON events USING gin (data);
7
SELECT * FROM events WHERE data @> '{"type": "login"}'::jsonb;  -- 走 GIN 索引

1
-- 手动更新统计信息
2
ANALYZE orders;
3

4
-- 或针对单列更新
5
ANALYZE orders (status);
6

7
-- 对数据分布不均的列，提高统计精度
8
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
9
ANALYZE orders;