PostgreSQL百万级URL记录查询性能优化：超25条返回空值

背景与问题 #

我们有一张存储百万级URL记录的url_details表，原需求是检索特定域名对应的URL，原SQL语句如下：

select distinct ud.url from url_details ud where ud.url_string ~ concat('^.*', :domain, '/?$') and (ud.active IS NOT NULL)

当前存在的问题：部分域名会返回1-2百万条记录，导致SQL执行极慢，还占用大量内存。

新增核心需求：如果查询返回的记录数超过25，则直接返回空值，同时必须重构查询来提升性能和内存利用率。

优化方案 #

核心优化思路 #

原查询的两个致命性能瓶颈：

1. distinct对百万级结果集去重，需要大量排序操作，内存和CPU开销极大
2. 正则表达式~ concat('^.*', :domain, '/?$')无法利用索引，触发全表扫描，速度极慢

针对这些问题，结合新需求，给出以下几种实用优化方案：

方案1：先判断数量再返回结果（避免全量拉取）

先统计符合条件的去重URL数量，若超过25则返回空，否则再查询具体URL。这样能彻底避免一次性拉取百万级数据：

WITH url_count AS (
    SELECT COUNT(DISTINCT url) AS total
    FROM url_details
    WHERE active IS NOT NULL
      AND (url_string LIKE concat('%', :domain) OR url_string LIKE concat('%', :domain, '/'))
)
SELECT 
    CASE 
        WHEN total <= 25 THEN (
            SELECT ARRAY_AGG(DISTINCT url)
            FROM url_details
            WHERE active IS NOT NULL
              AND (url_string LIKE concat('%', :domain) OR url_string LIKE concat('%', :domain, '/'))
        ) 
        ELSE NULL 
    END AS result_urls
FROM url_count;

这里把正则替换成LIKE，匹配域名结尾（带/或不带/的情况），比正则匹配效率高得多，且如果url_string有索引，部分场景能利用到索引加速。

方案2：限制查询行数，提前终止扫描（性能最优）

不需要全表统计，只要查到第26条记录就可以判定结果超过25，直接返回空。这种方式数据库会在找到26条数据后立即停止扫描，避免全表遍历：

WITH limited_urls AS (
    SELECT DISTINCT url
    FROM url_details
    WHERE active IS NOT NULL
      AND (url_string LIKE concat('%', :domain) OR url_string LIKE concat('%', :domain, '/'))
    LIMIT 26 -- 只取前26条，用来判断是否超过25
)
SELECT 
    CASE 
        WHEN (SELECT COUNT(*) FROM limited_urls) <= 25 THEN ARRAY_AGG(url) 
        ELSE NULL 
    END AS result_urls
FROM limited_urls;

这个方案的性能最好，因为LIMIT 26会让查询尽早终止，尤其是当符合条件的记录很多时，能节省大量时间。

方案3：添加合适的索引（从根源提速）

不管用哪种查询方案，添加合适的索引都能大幅提升性能。建议创建复合索引：

-- 基础复合索引：先过滤active，再匹配url_string
CREATE INDEX idx_active_urlstring ON url_details(active, url_string);

-- 覆盖索引：包含url字段，避免回表查询（性能更优）
CREATE INDEX idx_active_urlstring_include_url ON url_details(active, url_string) INCLUDE (url);

覆盖索引可以让数据库直接从索引中获取所需的url和url_string数据，不需要再去查询主表，进一步降低IO开销。

关键注意点 #

• 正则转LIKE：原正则的^.*前缀会让索引完全失效，换成LIKE的后缀匹配（假设域名是URL的结尾部分），能有效降低匹配开销
• 避免全量返回：通过计数或限制行数的方式，永远不要让数据库返回百万级结果集，这是解决内存占用问题的核心
• 索引适配：根据实际数据分布调整索引，比如如果active字段的非空比例很高，可能需要调整索引顺序

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