postgres-query-expert

PostgreSQL 查询专家

本技能是 PostgreSQL 16 的权威参考，涵盖查询构建、优化、架构管理和系统检查。

说明

1. 通用查询规范

• 语法：遵循 ANSI SQL 标准，但在能让逻辑更清晰或性能更好的情况下，优先使用 PostgreSQL 扩展（例如 DISTINCT ON、RETURNING、LATERAL、FILTER 子句）。
• 标识符：所有标识符均使用 snake_case。仅在绝对必要时为标识符加引号（"MyTable"）；优先使用小写且不加引号的名称。
• 安全性：
  • 参数化：始终使用参数（$1、$2、……）表示字面值。切勿直接注入用户输入。
  • 超时：对大型数据库进行探索性查询时，在查询前添加 SET LOCAL statement_timeout = '30s';。
  • 事务：对多步操作使用显式的 BEGIN 和 COMMIT 代码块。

2. 性能与优化

• 执行计划：使用 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) 诊断瓶颈。
• 危险信号：大型表上的 Seq Scan、较高的 Buffers: shared hit（内存使用量），或 Disk: read（I/O）。
• 索引：根据使用场景推荐特定的索引类型：
  • B-tree：用于标准的等值／范围（=、<、>）查询。
  • GIN：用于 JSONB（@>）或数组（&&）等复合类型，以及全文搜索。
  • GiST：用于几何数据和范围。
• CTE：使用公用表表达式（WITH）提升可读性。在 PG16+ 中，默认会对其进行优化（内联），除非指定 MATERIALIZED。

检查（Agent 能力）

探索新数据库时，使用以下查询了解其架构。

列出所有表

SELECT n.nspname AS schema,
       c.relname AS table,
       obj_description(c.oid) AS description
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

获取表列与类型

SELECT a.attname AS column,
       format_type(a.atttypid, a.atttypmod) AS type,
       a.attnotnull AS not_null,
       col_description(a.attrelid, a.attnum) AS comment
FROM pg_attribute a
WHERE a.attrelid = 'public.target_table_name'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

参考：数据查询（DQL）

高级聚合

• Filter 子句：count(*) FILTER (WHERE status = 'active')
• 分组集：GROUP BY GROUPING SETS ((brand), (brand, category), ())
• 任意值：any_value(col)（PG16+）返回组中的任意值。

窗口函数

对与当前行相关的一组表行进行计算。

SELECT dept,
       emp_no,
       salary,
       -- Rank employees by salary within department
       dense_rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank,
       -- Running total of salaries
       sum(salary) OVER (
         PARTITION BY dept
         ORDER BY salary
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM employees;

模式匹配

• LIKE：col LIKE 'foo%'（简单通配符）。
• ILIKE：col ILIKE 'foo%'（不区分大小写）。
• SIMILAR TO：col SIMILAR TO '[a-c]%'（SQL 正则表达式风格）。
• POSIX 正则表达式：
  • 区分大小写：col ~ '^[a-z]+$'
  • 不区分大小写：col ~* 'foo'

参考：数据修改（DML）

MERGE（Upsert／条件操作）

基于联接条件执行插入、更新或删除的标准 SQL 方法（PG15+）。

MERGE INTO wine_stock ws
USING wine_shipments s
  ON s.winery_id = ws.winery_id
 AND s.year = ws.year
WHEN MATCHED THEN
  UPDATE SET stock = ws.stock + s.count
WHEN NOT MATCHED THEN
  INSERT (winery_id, year, stock)
  VALUES (s.winery_id, s.year, s.count);

INSERT ... ON CONFLICT（传统 Upsert）

Postgres 特有的语法，处理简单的唯一键冲突时通常更简洁。

INSERT INTO kv_store (key, value)
VALUES ('config', '{"a":1}')
ON CONFLICT (key)
DO UPDATE SET value = EXCLUDED.value;

RETURNING 子句

立即返回已修改行的数据。

DELETE FROM archived_logs
WHERE created_at < NOW() - INTERVAL '1 year'
RETURNING id, created_at;

参考：特殊数据类型

JSONB（二进制 JSON）

存储和建立索引时，优先选择 jsonb 而非 json。

运算符	说明	示例
-> / ->>	获取元素（JSON／文本）	data->'key'
@>	包含（可建立索引）	data @> '{"tag": "urgent"}'
?	键存在	data ? 'error'
#-	删除路径	data #- '{info, sensitive}'

SQL/JSON 路径（PG12+）：

-- Find all items with price > 10
SELECT jsonb_path_query(data, '$.items[*] ? (@.price > 10)')
FROM orders;

数组

SELECT ARRAY[1,2,3];           -- Creation
SELECT (ARRAY[1,2,3])[1];      -- Access (1-based index)
SELECT 1 = ANY(arr_col);       -- Check if value exists in array
SELECT unnest(arr_col) FROM t; -- Expand array to rows

范围类型

适用于排期和有效期。

• tstzrange：带时区的时间戳范围。
• int4range、daterange：整数和日期范围。
• 重叠运算符（&&）：检查两个范围是否重叠。

SELECT *
FROM reservations
WHERE duration && tstzrange('2023-01-01 10:00', '2023-01-01 12:00');

参考：系统管理与统计信息

终止长时间运行的查询

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
  AND query_start < NOW() - INTERVAL '5 minutes';

检查表大小（磁盘使用量）

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS data_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

示例

场景 1：用于图／树数据的递归 CTE

遍历组织层级结构。

WITH RECURSIVE subordinates AS (
    -- Base case: the manager
    SELECT employee_id, manager_id, full_name, 0 AS level
    FROM employees
    WHERE employee_id = $1

    UNION ALL

    -- Recursive step: direct reports
    SELECT e.employee_id, e.manager_id, e.full_name, s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT *
FROM subordinates;

场景 2：用于“每个类别取前 N 条”的 Lateral Join

高效获取每位用户最新发布的 3 篇帖子。

SELECT u.username, p.title, p.created_at
FROM users u
CROSS JOIN LATERAL (
    SELECT title, created_at
    FROM posts
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) p
WHERE u.status = 'active';

场景 3：带排名的全文搜索

搜索博客表。

SELECT id,
       title,
       ts_rank(to_tsvector('english', title || ' ' || content), query) AS rank
FROM articles,
     to_tsquery('english', 'postgres | optimization') query
WHERE to_tsvector('english', title || ' ' || content) @@ query
ORDER BY rank DESC;