PostgreSQL：如何优化排除已插入行的随机选行插入查询？

问题核心 #

当前查询针对task_dispersal的每个id，从users中随机选取未分配过该order_id的用户插入task_pool，但随着数据量增长（task_pool近百万行、users近5万行），查询耗时达9.5秒，主要瓶颈在执行计划中重复的全表扫描和低效的随机选取逻辑。

先修正表结构笔误 #

你提供的表结构中重复定义了task_pool，推测第一个应为task_dispersal，正确结构应为：

create table users(
    user_id serial primary key,
    irellevant_columns varchar
);
create table task_dispersal(
    id serial primary key,
    irellevant_columns varchar
);
create table task_pool(
    task_id serial primary key,   
    order_id integer,
    user_id integer
);

关键优化点及方案 #

1. 添加复合索引消除全表扫描 #

执行计划显示，每个task_dispersal.id都会触发一次task_pool的全表扫描（Seq Scan on public.task_pool task_pool_1），每次过滤掉98万+行仅保留数百行，这是最大耗时点。添加复合索引让数据库快速定位每个order_id对应的已分配用户：

CREATE INDEX idx_task_pool_order_user ON task_pool(order_id, user_id);

该索引能直接支持子查询select user_id from task_pool where order_id = task_dispersal.id的快速查找，避免全表扫描。

2. 优化随机用户选取逻辑 #

原查询用tablesample bernoulli(.1)抽样后再排序随机，存在两个问题：抽样可能抽到已被排除的用户，且order by random()会带来额外排序开销。改为直接从符合条件的用户集合中随机选取，效率更高：

INSERT into task_pool(user_id, order_id)
SELECT u.user_id, td.id
FROM task_dispersal td
CROSS JOIN LATERAL (
    SELECT user_id
    FROM users
    WHERE user_id NOT IN (
        SELECT user_id FROM task_pool WHERE order_id = td.id
    )
    OFFSET floor(random() * (
        SELECT COUNT(*) 
        FROM users 
        WHERE user_id NOT IN (
            SELECT user_id FROM task_pool WHERE order_id = td.id
        )
    ))
    LIMIT 1
) u;

如果担心NOT IN的性能，也可以用LEFT JOIN替代：

INSERT into task_pool(user_id, order_id)
SELECT u.user_id, td.id
FROM task_dispersal td
CROSS JOIN LATERAL (
    SELECT u_inner.user_id
    FROM users u_inner
    LEFT JOIN task_pool tp ON tp.order_id = td.id AND tp.user_id = u_inner.user_id
    WHERE tp.task_id IS NULL
    OFFSET floor(random() * (
        SELECT COUNT(*) 
        FROM users u_inner
        LEFT JOIN task_pool tp ON tp.order_id = td.id AND tp.user_id = u_inner.user_id
        WHERE tp.task_id IS NULL
    ))
    LIMIT 1
) u;

3. 预计算已分配用户集合（可选） #

如果task_dispersal的数量较多，可提前将所有order_id对应的已分配用户存入临时表，避免重复查询：

-- 创建临时表存储每个order_id已分配的用户
CREATE TEMP TABLE assigned_users AS
SELECT order_id, array_agg(user_id) AS user_ids
FROM task_pool
GROUP BY order_id;

INSERT into task_pool(user_id, order_id)
SELECT u.user_id, td.id
FROM task_dispersal td
LEFT JOIN assigned_users au ON au.order_id = td.id
CROSS JOIN LATERAL (
    SELECT user_id
    FROM users
    WHERE (au.user_ids IS NULL OR user_id <> ALL(au.user_ids))
    OFFSET floor(random() * (
        SELECT COUNT(*) 
        FROM users
        WHERE (au.user_ids IS NULL OR user_id <> ALL(au.user_ids))
    ))
    LIMIT 1
) u;

临时表会自动在会话结束后销毁，适合一次性批量处理场景。

执行计划预期优化效果 #

添加索引后，子查询的全表扫描会变为索引扫描，单循环耗时从95ms左右降至毫秒级；优化随机选取逻辑后，避免了不必要的抽样和排序开销，整体耗时可大幅降低。

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