查询工具箱

可维护查询的基本形状

SELECT
  o.id,
  c.email,
  o.total_cents,
  o.placed_at
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = $1
  AND o.placed_at >= $2
ORDER BY o.placed_at DESC, o.id DESC
LIMIT $3;

这条查询明确了输出、连接条件、参数、稳定排序和上限。$1、$2、$3 由驱动绑定；不要用字符串拼接用户输入。

JOIN 的决策

目标	使用
只保留两边匹配行	`INNER JOIN` / `JOIN`
保留左表全部行	`LEFT JOIN`
判断相关行是否存在	`EXISTS`，常比“JOIN 后 DISTINCT”更清楚
找没有关联行的数据	`NOT EXISTS`，避免 `NOT IN` 的 `NULL` 语义陷阱

SELECT c.id, c.email
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
);

聚合与窗口不是一回事

GROUP BY 把多行折叠为一行；窗口函数保留明细行，同时在窗口内计算。

SELECT
  customer_id,
  id AS order_id,
  total_cents,
  row_number() OVER (
    PARTITION BY customer_id
    ORDER BY placed_at DESC, id DESC
  ) AS recency_rank,
  sum(total_cents) OVER (PARTITION BY customer_id) AS lifetime_cents
FROM orders;

CTE 的用途

CTE 应为复杂查询命名阶段，而不是自动优化按钮。

WITH recent_paid AS (
  SELECT customer_id, total_cents
  FROM orders
  WHERE status = 'paid'
    AND placed_at >= now() - interval '30 days'
)
SELECT customer_id, sum(total_cents) AS paid_cents
FROM recent_paid
GROUP BY customer_id;

分页

大结果集优先 keyset pagination：

SELECT id, placed_at, total_cents
FROM orders
WHERE (placed_at, id) < ($1, $2)
ORDER BY placed_at DESC, id DESC
LIMIT 50;

与很大的 OFFSET 相比，它不需要不断跳过前面的行，并在并发写入时更稳定。游标必须包含排序的全部键。

PostgreSQL 19 语法便利项

PostgreSQL 19（截至本页更新仍为 Beta）计划了几项小型语法新增。不要把这些语法发给更旧的服务器；以最终 release notes 为准。

GROUP BY ALL 自动按 target list 中所有非 aggregate、非 window 项分组，不必重复书写分组列表：

SELECT customer_id, status, count(*)
FROM orders
GROUP BY ALL;

窗口函数支持 IGNORE NULLS / RESPECT NULLS，适用于 lead()、lag()、first_value()、last_value() 和 nth_value()：

SELECT customer_id, placed_at,
       lag(placed_at) IGNORE NULLS OVER (
         PARTITION BY customer_id ORDER BY placed_at
       ) AS previous_order_at
FROM orders;

INSERT ... ON CONFLICT DO SELECT ... RETURNING 把 get-or-create 变成单条原子语句：要么插入新行，要么返回发生冲突的已有行。DO SELECT 必须提供 conflict_target 和 RETURNING 子句；可选的锁定子句（FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE、FOR KEY SHARE）会对冲突行加锁，防止并发更新。

INSERT INTO customers (email)
VALUES ($1)
ON CONFLICT (email) DO SELECT
RETURNING id;

见 PostgreSQL 19 INSERT 文档。

写查询前的检查

输出列是否稳定并最小化？
每个 JOIN 是否可能放大行数？
NULL 的含义是否明确？
排序是否有唯一的最终 tie-breaker？
参数是否由驱动绑定？
是否需要超时和结果行数上限？