PostgreSQL12慢SQL优化咨询：1200万行大表查询耗时4秒如何提速

核心问题定位 #

原查询耗时高的核心原因是索引失效和冗余关联/不合理写法，针对PostgreSQL 12环境优化方案如下：

1. 修正时间条件写法，解决索引失效 #

原查询对startts、stopts两个索引列使用了to_timestamp()函数和数值运算，导致现有联合索引完全无法命中，需修改为对常量做类型转换，保留索引列原生值：

-- 原写法（索引失效）
to_timestamp(e.startts) >= '2021-10-18T17:45:22Z' 
and to_timestamp(e.stopts-1) <= '2021-10-18T19:45:22Z'

-- 优化后写法（可命中索引）
e.startts >= extract(epoch from '2021-10-18T17:45:22Z'::timestamptz)
and e.stopts <= extract(epoch from '2021-10-18T19:45:22Z'::timestamptz) + 1

2. 替换更合适的覆盖索引 #

原有id + qualityid + startts + stopts联合索引不符合最左匹配规则（id是第一列但查询没有id过滤条件），建议替换为以下覆盖索引，查询可直接走索引无需回表：

CREATE INDEX idx_inspection_stat_filter ON public.inspectionresultsstatistic 
(qualityid, startts, stopts) 
INCLUDE (crateid, lineid, checkedcrates);

3. 删除冗余表关联 #

原查询关联了quality表，但没有用到该表任何返回字段，过滤条件qualityid=0可直接用主表e.qualityid=0，删除该表关联可减少一次关联开销。

4. 优化后完整SQL #

SELECT 
    checkedcrates, 
    pv.name as powervision, 
    cg.name as cgrp, 
    cs.name as csz, 
    c.name as cname
FROM public.inspectionresultsstatistic e
INNER JOIN crates c ON c.id = e.crateid
INNER JOIN lines l ON l.id = e.lineid
INNER JOIN powervisions pv ON pv.id = l.powervisionid
INNER JOIN cratesgroupscrates cgc ON c.id = cgc.crateid
INNER JOIN cratesgroups cg ON cg.id = cgc.crategroupid
INNER JOIN cratessizes cs ON cs.id = cgc.cratesizeid
WHERE
    e.qualityid = 0
    AND pv.name IN ('PV101')  
    AND c.name IN ('24603','104','136','154','186','106','156','216','246','206')
    AND cg.name IN ('Black','Blue','DLL','Green')
    AND cs.name IN ('30x40','60x40')
    AND e.startts >= extract(epoch from '2021-10-18T17:45:22Z'::timestamptz)
    AND e.stopts <= extract(epoch from '2021-10-18T19:45:22Z'::timestamptz) + 1
GROUP BY 
    powervision, 
    cgrp, 
    csz, 
    cname, 
    checkedcrates, 
    startts

可选优化项 #

其余关联表数据量均小于50行，查询开销极低，如有需要可针对各小表的name字段建普通索引，进一步加快过滤速度：

CREATE INDEX idx_powervisions_name ON powervisions(name);
CREATE INDEX idx_crates_name ON crates(name);
CREATE INDEX idx_cratesgroups_name ON cratesgroups(name);
CREATE INDEX idx_cratessizes_name ON cratessizes(name);

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