Firebird 3.0 SQL查询优化咨询：特定条件查询性能优化

这条查询的作用是筛选符合特定条件的记录，检测哪些DEVICEID的LAST_TICK值，大于指定DEVICEID={INTEGER VALUE}对应其他DEVICEID的已知LAST_TICK值。

原始SQL查询 #

select a.TABELA, a.DEVICEID, 
(select LAST_TICK from KNOWLEDGE b where b.DEVICEID={INTEGER VALUE} and b.CROSSID=a.DEVICEID and b.TABELA=a.TABELA) as MEU_TICK, 
max(LAST_TICK) as MAX_TICK 
from KNOWLEDGE a 
where a.DEVICEID=a.CROSSID 
group by a.TABELA, a.DEVICEID 
having max(LAST_TICK) > (select LAST_TICK from KNOWLEDGE b where b.DEVICEID={INTEGER VALUE} and b.CROSSID=a.DEVICEID and b.TABELA=a.TABELA) 
order by a.TABELA

小数据集（11条记录）下的性能统计 #

执行计划 #

PLAN (B INDEX (PK_KNOWLEDGE))
PLAN (B INDEX (PK_KNOWLEDGE))
PLAN SORT (A NATURAL)

性能指标详情 #

------ 性能信息 ------
Prepare time = 0ms
Execute time = 0ms
Current memory = 36.473.632
Max memory = 36.729.200
Memory buffers = 2.048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 65

执行计划分步解析 #

• 两个子查询的执行路径一致：Select Expression -> Singularity Check -> Filter -> Table "KNOWLEDGE" as "B" Access By ID -> Bitmap -> Index "PK_KNOWLEDGE" Unique Scan
• 主查询的执行路径：Select Expression -> Filter -> Aggregate -> Sort (record length: 214, key length: 176) -> Filter -> Table "KNOWLEDGE" as "A" Full Scan

补充：KNOWLEDGE表DDL语句（部分） #

CREATE TABLE KNOWLEDGE (
DEVICEID "INTEGER" NOT NULL /* ... */

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