PostgreSQL查询运行卡顿无返回结果，寻求性能优化方案

问题根源 #

从执行计划和表结构来看，查询完全卡顿的核心原因是左连接tblemployee时的OR条件+隐式类型转换：

• 连接条件(a.affectedemployeeid=e.cid or a.affectedemployee=e.cid or a.affectedemployee=e.employeenumber)包含多个OR分支，其中a.affectedemployee为文本类型，与e.cid（整数）、e.employeenumber（整数）比较时触发隐式类型转换，导致tblemployee的主键索引和employeenumber索引无法被有效利用。
• 执行计划显示，PostgreSQL对tblauditlog中符合时间条件的约56万条记录，每条都要全表扫描一次tblemployee（4.6万条），嵌套循环的总计算量达到56万×4.6万=2576亿次，直接导致查询卡死。

此外，tbluser的左连接存在小范围性能浪费：执行计划中对tbluser全表扫描过滤usertype<>250，虽返回行数少，但可通过索引优化减少扫描成本。

具体优化方案 #

1. 修复类型不匹配问题（基础优化） #

如果a.affectedemployee存储的是员工ID或工号（整数），建议将字段类型改为整数；若必须保留文本类型，查询中显式转换类型以匹配索引：

select a.auditdate,b.description as auditcategory,remoteaddress,u.name as user1,e.name || '[' || e.employeenumber || ']' as employee,a.additionalinfo 
from tblauditlog a
inner join tblauditcategory b on b.cid = a.auditcategory and b.cid<>756
left outer join tbluser u on a.userid=u.cid and u.usertype<>250
-- 显式转换类型，让条件命中索引
left outer join tblemployee e on 
    a.affectedemployeeid = e.cid 
    OR (a.affectedemployee::integer = e.cid) 
    OR (a.affectedemployee::integer = e.employeenumber)
where auditdate >= '2022-09-01' and auditdate <= '2022-09-15' 
order by a.auditdate desc

2. 将OR连接拆分为UNION ALL（推荐方案） #

OR条件是嵌套循环的性能杀手，将查询拆分为三个独立的左连接分支，用UNION ALL合并结果，每个分支都能利用tblemployee的索引：

-- 分支1：通过affectedemployeeid关联
select 
    a.auditdate,
    b.description as auditcategory,
    remoteaddress,
    u.name as user1,
    e.name || '[' || e.employeenumber || ']' as employee,
    a.additionalinfo 
from tblauditlog a
inner join tblauditcategory b on b.cid = a.auditcategory and b.cid<>756
left outer join tbluser u on a.userid=u.cid and u.usertype<>250
left outer join tblemployee e on a.affectedemployeeid = e.cid
where auditdate >= '2022-09-01' and auditdate <= '2022-09-15'

UNION ALL

-- 分支2：通过affectedemployee匹配cid，排除已被分支1匹配的记录
select 
    a.auditdate,
    b.description as auditcategory,
    remoteaddress,
    u.name as user1,
    e.name || '[' || e.employeenumber || ']' as employee,
    a.additionalinfo 
from tblauditlog a
inner join tblauditcategory b on b.cid = a.auditcategory and b.cid<>756
left outer join tbluser u on a.userid=u.cid and u.usertype<>250
left outer join tblemployee e on a.affectedemployee::integer = e.cid
where auditdate >= '2022-09-01' and auditdate <= '2022-09-15'
and a.affectedemployeeid is null

UNION ALL

-- 分支3：通过affectedemployee匹配employeenumber，排除已被前两个分支匹配的记录
select 
    a.auditdate,
    b.description as auditcategory,
    remoteaddress,
    u.name as user1,
    e.name || '[' || e.employeenumber || ']' as employee,
    a.additionalinfo 
from tblauditlog a
inner join tblauditcategory b on b.cid = a.auditcategory and b.cid<>756
left outer join tbluser u on a.userid=u.cid and u.usertype<>250
left outer join tblemployee e on a.affectedemployee::integer = e.employeenumber
where auditdate >= '2022-09-01' and auditdate <= '2022-09-15'
and a.affectedemployeeid is null
and a.affectedemployee::integer not in (select cid from tblemployee)

order by auditdate desc;

3. 优化tbluser索引 #

当前idx_tbluser_utype为(cid, usertype)，针对查询条件u.usertype<>250且a.userid=u.cid，创建反向索引以快速过滤不符合条件的记录：

CREATE INDEX idx_tbluser_usertype_cid ON tbluser(usertype, cid);

4. 覆盖索引优化（可选） #

为tblauditlog创建覆盖索引，包含查询所需的所有字段，减少回表查询的IO成本：

CREATE INDEX idx_tblauditlog_auditdate_includes ON tblauditlog(auditdate) 
INCLUDE (auditcategory, userid, remoteaddress, additionalinfo, affectedemployeeid, affectedemployee);

临时应急方案 #

若无法立即修改表结构或创建索引，可临时禁用嵌套循环，强制PostgreSQL使用哈希连接或合并连接：

set enable_nestloop = off;
-- 执行原查询
set enable_nestloop = on; -- 查询完成后恢复默认设置

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