记一次postgresql查询优化

一、场景介绍

1、需求

根据schema_1中多表联查结果，对相应schema_2中数据进行删除操作。

2、表结构

模式	表名	表结构
schema_1	table_1	id(varchar 32)	pk	主键
	table_2	id(varchar 32)	pk	主键
		table_1_id(varchar 32)	INDEX	table_1表主键
	table_3	id(varchar 32)	pk	主键
		table_2_id(varchar 32)	INDEX	table_2表主键
schema_2	table_3	id	pk	主键

 

表结构说明：① 从schema_1中三表联查得到table_3表相应id，根据该结果集对schema_2中table_3数据进行删除。

                      ② schema_1中table_1、table_2、table_3数据依次增多，且数据量级很大。

                      ③ schema_1中table_3数据为schema_2中table_3数据子集。

二、原始方案及结果

DELETE
FROM schema_2.table_3
WHERE "id" IN
(
    SELECT t3."id"
    FROM schema_1.table_3 t3
    JOIN schema_1.table_2 t2
    ON t3.table_2_id = t2."id"
    JOIN schema_1.table_1 t1
    ON t2.table_1_id = t1."id"
    WHERE ...
)

该方案子查询由table_3开始依次联查table_2、table_1，执行效率极低，分析SQL显示子查询并未使用到table_3索引。

单独执行子查询效率正常，且索引使用正常。

三、优化方案

DELETE
FROM schema_2.table_3
WHERE "id" IN
(
    SELECT t3."id"
    FROM schema_1.table_1 t1
    JOIN schema_1.table_2 t2
    ON t2.table_1_id = t1."id"
    JOIN schema_1.table_3 t3
    ON t3.table_2_id = t2."id"
    WHERE ...
)

子查询改为由数据量小的table_1依次联查至数据量大的table_3，此时执行效率大大降低，分析显示使用了table_3索引

四、总结

IN中子查询语句如使用联表查询，需使用小表驱动大表方式，否则在数据量巨大的场景下，导致大表索引失效，执行效率极低。