PostgreSQL 16.2：优化随机无重复插入task_pool的SQL查询

场景1：task_pool初始为空（当前你的情况） #

因为初始无已插入数据，无需排除任何user_id，核心优化是仅执行一次全局随机采样，再将采样结果分配给各个order_id，避免原语句中100次重复的全表随机排序：

WITH random_users AS (
    -- 一次性生成1000个随机user_id（100个order_id × 10个/每个）
    SELECT user_id, (row_number() OVER () - 1) / 10 AS group_id
    FROM users
    ORDER BY random()
    LIMIT 1000
),
order_groups AS (
    -- 给每个order_id分配对应分组ID
    SELECT order_id, row_number() OVER () - 1 AS group_id
    FROM task_dispersal
)
INSERT INTO task_pool(clicker_id, order_id)
SELECT ru.user_id, og.order_id
FROM random_users ru
JOIN order_groups og ON ru.group_id = og.group_id;

该方案仅执行一次ORDER BY RANDOM()，相比原语句的100次重复操作，性能提升显著，弱性能服务器上可控制在几秒内完成。

场景2：task_pool已有数据（通用重复执行场景） #

如果后续需要重复执行分配逻辑（已有部分用户被关联到order_id），可从以下方向优化：

1. 优化索引 #

给task_pool创建复合索引，加速已分配用户的查询：

CREATE INDEX idx_task_pool_order_clicker ON task_pool(order_id, clicker_id);

该索引会让SELECT clicker_id FROM task_pool WHERE order_id = TD.order_id变为高效的索引扫描，大幅降低子查询开销。

2. 替换ORDER BY RANDOM()的高效随机采样 #

避免全表随机排序，改用随机偏移量+行号的方式选取用户：

INSERT into task_pool(clicker_id, order_id)
SELECT U.user_id, TD.order_id
FROM task_dispersal TD
CROSS JOIN LATERAL (
    WITH available_users AS (
        -- 为当前order_id的可用用户分配行号
        SELECT user_id, row_number() OVER () AS rn
        FROM users Us
        WHERE user_id NOT IN (
            SELECT clicker_id FROM task_pool WHERE order_id = TD.order_id
        )
    ),
    random_rns AS (
        -- 生成不重复的随机行号（生成2倍量避免重复）
        SELECT DISTINCT floor(random() * (SELECT COUNT(*) FROM available_users)) + 1 AS rn
        FROM generate_series(1, TD.to_disp * 2)
        LIMIT TD.to_disp
    )
    -- 通过随机行号选取目标用户
    SELECT au.user_id
    FROM available_users au
    JOIN random_rns rr ON au.rn = rr.rn
) U;

这种方式用顺序排序（row_number）替代随机排序，计算开销远低于ORDER BY RANDOM()。

3. 利用连续user_id特性（如果适用） #

如果users表的user_id是连续无缺失的serial值（当前表结构为serial且无用户删除操作），可直接生成随机整数并验证有效性，完全避免扫描users表：

INSERT into task_pool(clicker_id, order_id)
SELECT U.user_id, TD.order_id
FROM task_dispersal TD
CROSS JOIN LATERAL (
    -- 生成随机user_id，去重后筛选可用项，取足够数量
    SELECT DISTINCT (floor(random() * (SELECT MAX(user_id) FROM users)) + 1) AS user_id
    FROM generate_series(1, TD.to_disp * 2)
    WHERE user_id NOT IN (SELECT clicker_id FROM task_pool WHERE order_id = TD.order_id)
    LIMIT TD.to_disp
) U;

该方案性能最优，但依赖user_id连续的前提；若存在用户删除导致ID不连续，需添加user_id IN (SELECT user_id FROM users)的存在性检查。

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