PostgreSQL子查询LIMIT分配问题：如何分摊总限额至多子查询

这个问题我之前处理过好几次——当某个高匹配度的子查询把总LIMIT全占了，其他维度的匹配结果根本出不来，确实挺闹心的。下面给你两个实用的解决方案，按需选择：

方案一：固定权重分配限额（简单直接） #

最容易实现的方式是给每个子查询单独设置LIMIT，总和控制在1000以内。你可以根据业务优先级调整每个子查询的配额，比如电话/邮箱匹配更精准，给更高的限额，姓名匹配给中等，地址匹配稍低。

示例代码：

WITH name_matches AS (
    SELECT * FROM contacts
    WHERE name ILIKE '%目标姓名%'
      -- 保留原逻辑：过滤掉空邮箱/电话/地址的常见姓名
      AND (email IS NOT NULL OR phone IS NOT NULL OR address IS NOT NULL)
    LIMIT 200  -- 姓名匹配分配200条额度
),
phone_matches AS (
    SELECT * FROM contacts
    WHERE phone = '目标电话'
    LIMIT 300  -- 电话匹配分配300条额度
),
email_matches AS (
    SELECT * FROM contacts
    WHERE email ILIKE '%目标邮箱%'
    LIMIT 300  -- 邮箱匹配分配300条额度
),
address_matches AS (
    SELECT * FROM contacts
    WHERE address ILIKE '%目标地址%'
    LIMIT 200  -- 地址匹配分配200条额度
)
-- 合并所有子查询结果，可按匹配优先级排序
SELECT * FROM phone_matches
UNION ALL
SELECT * FROM email_matches
UNION ALL
SELECT * FROM name_matches
UNION ALL
SELECT * FROM address_matches
-- 保险起见，最后再加总LIMIT防止子查询因特殊情况超量返回
LIMIT 1000;

补充说明： #

• 如果担心同一个联系人被多个子查询匹配导致重复返回，可以把UNION ALL换成UNION（自动去重，但会有排序开销），或者用DISTINCT ON(id)配合ORDER BY保留优先级更高的匹配记录。
• 配额比例完全自定义，比如把电话/邮箱的额度提高到350，姓名/地址各150，只要总和不超过1000就行。

方案二：动态分配限额（灵活适配匹配数量） #

如果不想固定配额，希望根据每个子查询的实际匹配数动态分配额度（比如某个维度只有10条匹配结果，就把剩余额度分给其他维度），可以先统计各维度的匹配总数，再按比例分配限额。

示例代码：

-- 第一步：统计每个维度的匹配总数
WITH match_counts AS (
    SELECT 
        'name' AS type,
        COUNT(*) AS cnt
    FROM contacts
    WHERE name ILIKE '%目标姓名%'
      AND (email IS NOT NULL OR phone IS NOT NULL OR address IS NOT NULL)
    UNION ALL
    SELECT 'phone' AS type, COUNT(*) FROM contacts WHERE phone = '目标电话'
    UNION ALL
    SELECT 'email' AS type, COUNT(*) FROM contacts WHERE email ILIKE '%目标邮箱%'
    UNION ALL
    SELECT 'address' AS type, COUNT(*) FROM contacts WHERE address ILIKE '%目标地址%'
),
-- 第二步：计算总匹配数，生成各维度的动态配额
total_stats AS (
    SELECT SUM(cnt) AS total FROM match_counts
),
allocation AS (
    SELECT 
        type,
        -- 如果总匹配数≤1000，取全部结果；否则按比例分配额度
        LEAST(cnt, ROUND(1000 * cnt / (SELECT total FROM total_stats))::INT) AS quota
    FROM match_counts
    CROSS JOIN total_stats
),
-- 第三步：按动态配额获取各维度结果
name_matches AS (
    SELECT * FROM contacts
    WHERE name ILIKE '%目标姓名%'
      AND (email IS NOT NULL OR phone IS NOT NULL OR address IS NOT NULL)
    LIMIT (SELECT quota FROM allocation WHERE type = 'name')
),
phone_matches AS (
    SELECT * FROM contacts
    WHERE phone = '目标电话'
    LIMIT (SELECT quota FROM allocation WHERE type = 'phone')
),
email_matches AS (
    SELECT * FROM contacts
    WHERE email ILIKE '%目标邮箱%'
    LIMIT (SELECT quota FROM allocation WHERE type = 'email')
),
address_matches AS (
    SELECT * FROM contacts
    WHERE address ILIKE '%目标地址%'
    LIMIT (SELECT quota FROM allocation WHERE type = 'address')
)
-- 合并结果并按优先级排序
SELECT * FROM phone_matches
UNION ALL
SELECT * FROM email_matches
UNION ALL
SELECT * FROM name_matches
UNION ALL
SELECT * FROM address_matches
LIMIT 1000;

补充说明： #

• 这个方案适合数据波动大的场景，不会浪费配额，但多了两次统计查询，性能上会有轻微开销，数据量极大时需要权衡。
• 可以在allocation步骤里调整配额逻辑，比如给电话/邮箱匹配设置最低配额，避免因比例分配导致这类精准匹配的结果太少。

额外优化建议 #

1. 索引优化：给电话字段建B-tree索引，姓名/邮箱/地址字段如果用模糊查询，建议用pg_trgm扩展创建GIN索引，能大幅提升子查询的LIMIT执行速度。
2. 优先级排序：在最终的SELECT里加上ORDER BY，把更精准的匹配（比如电话、邮箱）放在前面，确保用户先看到最相关的结果。

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