PostgreSQL 查询优化：从龟速到亚毫秒级

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;

-- 为相关列创建扩展统计信息
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;

CREATE INDEX idx_orders_created_at ON orders (created_at);

-- 查询：WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- 好：先等值，然后范围，然后排序
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- 差：范围在等值之前
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

-- 我们经常运行：
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

-- 只有 2% 的订单是 'pending' 状态，但我们不断在查询它们
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- 另一个常见模式：软删除
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- 唯一约束仅针对活跃记录
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

-- JSONB 包含查询
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- 现在这个很快：
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- 全文搜索
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);

-- IP 范围查找
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- 时间范围（日程冲突）
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- 查找重叠的预订：
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

-- 适合对大文本列进行纯等值查找
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- 这个很快：
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- 这个无法使用 hash 索引：
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

-- 查找未使用的索引
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- 排除主键和唯一约束
  )
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查找重复索引（相同的列，相同的顺序）
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

# Python / SQLAlchemy —— 经典陷阱
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # 这会为每个订单触发一次单独的查询
    print(f"Customer: {order.customer.name}")

-- 查询 1：获取所有待处理订单
SELECT * FROM orders WHERE status = 'pending';
 
-- 查询 2：获取订单 1 的客户
SELECT * FROM customers WHERE id = 101;
 
-- 查询 3：获取订单 2 的客户
SELECT * FROM customers WHERE id = 102;
 
-- ...查询 502：获取订单 500 的客户
SELECT * FROM customers WHERE id = 600;

-- 记录所有耗时超过 0 毫秒的查询（即所有查询）
SET log_min_duration_statement = 0;
 
-- 生产环境更好的做法：只记录慢查询
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50 毫秒阈值
SELECT pg_reload_conf();

-- 为慢查询启用 auto_explain
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100 毫秒
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

# SQLAlchemy —— joinedload 在同一个查询中获取客户
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

-- 方案 1：单个 JOIN（如果是一对多关系可能产生重复数据）
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- 方案 2：两次查询（如果 orders 列很多，数据传输更少）
SELECT * FROM orders WHERE status = 'pending';
-- 应用程序收集 order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);

-- WHERE 中的子查询 —— 通常没问题，有时很糟糕
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN —— 通常是最佳选择
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE —— PG 11 及以下会物化，PG 12+ 会优化
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

-- EXISTS —— 找到第一个匹配就停止，适合检查存在性
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- IN 加子查询 —— 先构建完整的结果集
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN —— 如果 returns 中每个 order 有多行，可能产生重复
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

-- 如果子查询返回很少的行，IN 完全没问题
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);

-- 差：获取所有 25 列，包括一个有数 KB 数据的 TEXT 列
SELECT * FROM products WHERE category = 'electronics';
 
-- 好：只获取你需要的
SELECT id, name, price FROM products WHERE category = 'electronics';

-- 慢：相关子查询每行执行一次
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- 快：窗口函数单次扫描计算
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

-- 慢：相关子查询
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- 快：DISTINCT ON（PostgreSQL 特有，非常有用）
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- 也很快：ROW_NUMBER 窗口函数
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);

-- 第 1 页：快
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- 第 500 页：慢 —— PostgreSQL 必须扫描并丢弃 9980 行
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- 第 5000 页：非常慢 —— 扫描 99980 行只为返回 20 行
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

-- 第一页
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- 下一页：使用上一页最后一个 id
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

-- 多排序列的键集分页
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;

-- 分析单个表
ANALYZE orders;
 
-- 分析整个数据库
ANALYZE;
 
-- 查看某列的当前统计信息
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

-- 为分布偏斜的列增加统计信息目标
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- 检查 autovacuum 状态
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 对高写入表更积极的 autovacuum
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- 1% 死元组时 vacuum（默认 20%）
  autovacuum_analyze_scale_factor = 0.005,   -- 0.5% 变更时 analyze（默认 10%）
  autovacuum_vacuum_cost_delay = 2           -- 更快的 vacuum（新版 PG 默认 2 毫秒）
);

-- 估算表膨胀（简化版）
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

pg_repack --table orders --no-kill-backend -d mydb

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; 池模式
pool_mode = transaction
 
; 池大小
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; 超时
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

const pool = new Pool({
  host: "127.0.0.1",
  port: 6432, // PgBouncer 端口，不是 PostgreSQL 端口
  database: "mydb",
  max: 20, // 与 PgBouncer 的 default_pool_size 匹配
  statement_timeout: 30000,
  // 为 PgBouncer 事务模式禁用预处理语句
  prepare: false,
});

-- 临时记录所有超过 100 毫秒的查询
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

-- 按总执行时间排名的前 10 条查询
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <那条慢查询>;

-- 统计信息上次更新是什么时候？
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

ANALYZE orders;

-- 这个表上有哪些索引？
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

-- 并发创建索引（不锁表）
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- 验证索引被使用
EXPLAIN (ANALYZE, BUFFERS) <那条慢查询>;

-- work_mem：排序和哈希连接的内存（每次操作，不是每个连接）
-- 默认是 4MB，对复杂查询来说太低
SET work_mem = '64MB';  -- 试试这个然后重新运行 EXPLAIN ANALYZE
 
-- effective_cache_size：告诉规划器预期有多少磁盘缓存
-- 设为总内存的约 75%
SHOW effective_cache_size;
 
-- random_page_cost：随机 I/O 与顺序 I/O 的比率
-- 默认是 4.0，SSD 设为 1.1-1.5
SHOW random_page_cost;

-- 重置 pg_stat_statements 以查看更改后的新数据
SELECT pg_stat_statements_reset();
 
-- 一小时/一天后再检查
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

-- 1. 安装 pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. 启用慢查询日志（50 毫秒是我的阈值）
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. 适合 SSD 的设置
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. 从默认的贫血值增加 work_mem
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. 将 effective_cache_size 设为内存的 75%
-- 对于 16GB 的服务器：
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. 将 shared_buffers 设为内存的 25%
-- 对于 16GB 的服务器：
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. 提高 autovacuum 响应性
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- 应用更改
SELECT pg_reload_conf();
-- 注意：shared_buffers 需要重启

-- 需要关注的表
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;