PostgreSQL请求表多维度限制查询及索引优化问询

表结构 #

请求暂存表request的建表语句：

CREATE TABLE request (
    request_id varchar(64) NOT NULL,
    company_id varchar(64) NOT NULL,
    department_id varchar(64) NOT NULL,
    created_time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_time timestamp NULL,
    status varchar(10) NULL,
    retry_counter int4 NULL,
    next_retry_time timestamp NULL,
    lock_status varchar(10) NULL,
    request_type varchar(16) NULL,
    request_data varchar(16384) NULL,
    priority int4 NULL,
    CONSTRAINT request_pkey PRIMARY KEY (company_id, request_id)
);

该表用于暂存待处理请求，完成后定期清理，另有其他表负责跟踪请求状态、触发重试等。最坏情况下数据量可达数百万条。

查询需求 #

• 每个company_id-department_id组合最多获取100条请求（公司与部门为一对多关系）
• 每个company_id最多获取500条请求（旗下所有部门请求数总和≤500）
• 单次查询总请求数最多2000条
• 按priority降序获取请求

SQL实现 #

以下SQL通过嵌套窗口函数实现多维度限流：

WITH dept_limited AS (
    -- 第一步：按公司+部门分组，取每组前100条高优先级请求
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY company_id, department_id ORDER BY priority DESC) AS dept_row_num
    FROM request
    -- 若需过滤特定状态（如待处理），可在此添加WHERE条件，例如：WHERE status = 'PENDING'
),
company_limited AS (
    -- 第二步：在公司维度累计请求数，保留累计不超过500的记录
    SELECT 
        *,
        SUM(CASE WHEN dept_row_num <= 100 THEN 1 ELSE 0 END) OVER (
            PARTITION BY company_id 
            ORDER BY priority DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS company_total
    FROM dept_limited
    WHERE dept_row_num <= 100
)
-- 第三步：取总请求数前2000条
SELECT 
    request_id, company_id, department_id, created_time, last_updated_time,
    status, retry_counter, next_retry_time, lock_status, request_type, request_data, priority
FROM company_limited
WHERE company_total <= 500
ORDER BY priority DESC
LIMIT 2000;

索引建议 #

针对数百万级数据量的性能优化，建议创建以下复合索引：

1. 核心查询索引：

CREATE INDEX idx_request_priority_company_dept ON request (priority DESC, company_id, department_id);

该索引直接匹配查询的排序规则和分组维度，能大幅减少窗口函数的计算开销，让数据库快速定位高优先级请求。

2. 可选过滤优化索引：
   如果查询需要过滤特定状态（如待处理的请求），建议扩展索引：

CREATE INDEX idx_request_status_priority_company_dept ON request (status, priority DESC, company_id, department_id);

3. 主键索引说明：
   现有主键(company_id, request_id)主要用于唯一性约束，对本次查询的排序分组场景帮助有限，无需调整，确保唯一性校验正常生效即可。

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