Ubuntu 上使用 pgAdmin 进行 PostgreSQL 查询优化的实用清单
一 识别慢 SQL 与执行计划分析
- 启用并查询全局 SQL 统计：安装扩展并重启数据库后，创建扩展，使用视图按总耗时、调用次数定位问题语句；同时可在日志中打印超过阈值的慢 SQL，便于与业务方对齐。示例要点：
- 配置：shared_preload_libraries=‘pg_stat_statements’；重启；CREATE EXTENSION pg_stat_statements;
- 查询：按 max_time、calls 排序找出 TOP SQL；日志阈值：log_min_duration_statement=10000（10 秒）。
- 在 pgAdmin 中使用 EXPLAIN 家族解读计划：优先使用 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 观察是否出现 Seq Scan、是否走索引、实际行数与循环次数、Shared Hit/Read 比例，从而判断 I/O 与计划质量。
- 快速定位全表扫描与索引缺失：查询 pg_stat_all_tables 中 seq_scan 多且表较大的对象，结合业务过滤条件评估是否缺少索引或统计信息不准。
二 索引策略与维护
- 选对索引类型：常用 B-Tree；等值查询可考虑 Hash（不支持范围）；全文与数组/JSONB 用 GIN；地理空间用 GiST；大表按块范围查询用 BRIN。
- 设计复合索引遵循最左前缀，必要时建立覆盖索引减少回表；对高选择性且常出现在 WHERE/ORDER BY 的列优先建索引；避免对索引列做函数或表达式计算以免索引失效。
- 结合业务建立部分索引（Partial Index）与表达式索引（Expression Index），例如对布尔状态列建立部分索引以加速常见过滤。
- 维护统计与碎片：定期执行 ANALYZE 更新统计信息；对高写入表在维护窗口执行 REINDEX/VACUUM（或 VACUUM FULL）以回收碎片；避免过度索引以免写放大。
三 SQL 写法与查询重构
- 避免 SELECT *，只返回必要列；减少大结果集排序与传输开销。
- 谨慎使用 OR，改写为 UNION ALL（在确认无重复且业务允许时）以避免索引失效与额外去重成本。
- 子查询大量数据时优先 EXISTS 替代 IN，减少中间结果物化与比较成本。
- 大数据量排序/分页避免大 OFFSET：采用“索引覆盖 + 延迟关联”或基于游标的 keyset 分页，显著降低扫描与排序成本。
- 深分页示例重构：
- WITH ids AS (SELECT id FROM logs ORDER BY create_time DESC LIMIT 10 OFFSET 100000)
SELECT l.* FROM logs l JOIN ids ON l.id = ids.id;
- 善用 CTE 分解复杂逻辑，必要时用窗口函数替代自连接，减少重复扫描与中间结果。
四 配置参数与资源优化
- 适度提升会话级排序/哈希内存：根据 work_mem 与并发度评估调大（如由默认 4MB 提升到几十 MB），降低磁盘临时文件与排序溢写。
- 调整成本常数以更贴合硬件：如 seq_page_cost、random_page_cost、cpu_tuple_cost、cpu_index_tuple_cost、effective_cache_size 等；SSD/内存充足时可降低 random_page_cost，使索引扫描更被青睐。
- 提升统计质量与计划稳定性：确保 autovacuum 正常运行以周期执行 ANALYZE；在统计失真或数据分布剧变后手动 ANALYZE 关键表。
五 表设计与数据加载实践
- 字段类型选择：能用 int/bigint 就不用字符型存储数值；只有在需要精确小数（如金额）时才用 numeric，以降低存储与比较开销。
- 时间类型建议使用 timestamp with time zone（内部以 UTC 存储并自动时区转换），避免跨时区业务的时间歧义。
- 批量导入优先 COPY，比逐条 INSERT 或 VALUES 批量插入快一个数量级；大数据量加载可先卸索引/约束、导入后再重建。
- 大表物理顺序与查询模式匹配时，可在低峰期使用 CLUSTER table USING index 提升顺序扫描与范围查询效率（注意会重写表并阻塞 DML）。
