如何优化PostgreSQL下查询GraphQL Connections的SQL性能并解决游标空值问题

1. 替换行级子查询为一次性游标预查询 #

你之前加COALESCE后性能暴跌的核心原因是WHERE条件里的子查询属于关联子查询，会逐行执行，10万条数据场景下会触发20万次子查询调用，开销自然飙升。
改成CTE（公共表表达式）一次性查询两个游标对应的排序字段值，全程仅执行2次游标查询，性能和最初的版本几乎一致，同时天然处理游标不存在的空值场景：

-- 入参：$1=after时间戳、$2=before时间戳、$3=first值、$4=last值
WITH cursor_vals AS (
    SELECT
        (SELECT title FROM posts WHERE created_at = $1) AS after_title,
        (SELECT title FROM posts WHERE created_at = $2) AS before_title
)
SELECT * FROM (
    SELECT * FROM (
        SELECT p.* FROM posts p, cursor_vals cv
        WHERE (cv.after_title IS NULL OR p.title > cv.after_title)
          AND (cv.before_title IS NULL OR p.title < cv.before_title)
        ORDER BY p.title
        LIMIT $3
    ) AS forward_pagination_result
    ORDER BY title DESC
    LIMIT $4
) AS backward_pagination_result
ORDER BY title;

如果传入的游标不存在，对应的after_title/before_title会返回NULL，此时WHERE条件中对应的判断自动跳过，不会过滤有效数据，完全符合业务需求。

2. 加覆盖索引把性能压到5ms以内 #

给posts表创建联合索引，同时覆盖排序字段和游标查询字段，不需要回表也不需要额外排序，性能还能再提升：

-- 按title排序的场景创建该索引
CREATE INDEX idx_posts_title_created_at ON posts (title, created_at);
-- 如果有其他sortBy字段，对应创建联合索引即可，比如按更新时间排序就建idx_posts_updated_at_created_at

3. 多排序字段场景的可选优化 #

如果需要支持任意sortBy字段，也可以在应用层做一次轻量批量查询：拿到after、before游标后，一次性查询两个游标是否存在、对应的排序值是什么，仅多1次数据库调用，之后直接拼接不带子查询的WHERE条件，性能比CTE写法还要更高。

内容的提问来源于stack exchange，提问作者ahamid555