SQL查询性能对比：两次子查询与EXISTS写法孰优？

背景与查询改写 #

我正在编辑生成SQL WHERE子句的Java StringTemplate4文件，原查询包含两次重复的IN子查询，现已改为EXISTS写法。

原查询 #

where
campaign.id IN (
    select entity_id from campaign.measurement_metadata measurement
    where
    AND UPPER(measurement.measurement_partner) IN (
        'KOCHAVA'  , 'SPAA'  , 'IAS' 
    )
    AND (measurement.measurement_partner_metadata ->> 'enabled') =
        'true'
)
OR
flight.id IN (
    select entity_id from campaign.measurement_metadata measurement
    where
    AND UPPER(measurement.measurement_partner) IN (
        'KOCHAVA'  , 'SPAA'  , 'IAS' 
    )
    AND (measurement.measurement_partner_metadata ->> 'enabled') =
        'true'
)

改写后的查询 #

where
EXISTS (
    select 1 from campaign.measurement_metadata measurement
    where measurement.entity_id IN (campaign.id, flight.id)
    AND UPPER(measurement.measurement_partner) IN (
        'KOCHAVA' 
    )
    AND (measurement.measurement_partner_metadata ->> 'enabled') =
        'true'
)

核心疑问 #

• UPPER函数是否会导致索引失效？
• 索引能否优化IN子句？
• JSONB的->>操作是否会大幅降低索引效率？
• 新查询性能是否不逊于原查询，背后原因是什么？

数据库表结构 #

CREATE TABLE campaign.measurement_metadata
(
    created TIMESTAMP NOT NULL DEFAULT current_timestamp,
    updated TIMESTAMP NOT NULL DEFAULT current_timestamp,
    entity_id UUID NOT NULL,
    entity_type TEXT NOT NULL,
    measurement_partner TEXT NOT NULL,
    measurement_partner_metadata JSONB NOT NULL,
    PRIMARY KEY (entity_id, entity_type, measurement_partner)
);

CREATE TRIGGER set_timestamp
    BEFORE UPDATE ON campaign.measurement_metadata
    FOR EACH ROW
    EXECUTE PROCEDURE campaign.trigger_set_timestamp();

CREATE INDEX measurement_metadata_idx ON campaign.measurement_metadata (entity_id, entity_type, measurement_partner);

分析与结论 #

1. UPPER函数对索引的影响 #

当前的measurement_metadata_idx索引存储的是measurement_partner的原始值，使用UPPER(measurement_partner)时，数据库无法直接利用该索引——因为需要对字段值做转换计算，索引无法匹配转换后的值。
如果要优化这个条件，可以创建函数索引：

CREATE INDEX idx_measurement_partner_upper ON campaign.measurement_metadata (UPPER(measurement_partner));

创建后UPPER(measurement_partner) IN (...)的条件就能直接走索引，避免全表扫描。

2. 索引对IN子句的优化 #

无论是原查询中campaign.id IN (子查询)，还是改写后measurement.entity_id IN (campaign.id, flight.id)，entity_id作为主键和现有索引的首列，数据库都能高效利用索引：

• 原查询的子查询会基于entity_id索引匹配符合条件的行；
• 改写后的IN (campaign.id, flight.id)会被数据库转换为等价的OR逻辑，通过索引快速定位这两个entity_id对应的行，性能很高。

3. JSONB->>操作的索引效率 #

measurement_partner_metadata ->> 'enabled' = 'true'属于JSONB字段的提取操作，默认无法利用现有索引——因为需要实时解析JSON内容并提取字段值，会增加计算开销。
如果这类查询频繁，可以创建表达式索引：

CREATE INDEX idx_metadata_enabled ON campaign.measurement_metadata ((measurement_partner_metadata ->> 'enabled'));

创建后该条件就能走索引，大幅提升过滤效率。

4. 新旧查询的性能对比 #

新的EXISTS查询性能明显不逊于原查询，甚至更优，原因如下：

• 原查询需要执行两次完全相同的子查询，再合并OR的结果，存在重复计算；
• 改写后的EXISTS只需要执行一次子查询，同时匹配campaign.id和flight.id，避免了重复逻辑，减少了数据库的执行开销；
• 另外改写后的查询还减少了measurement_partner的匹配范围（从3个值变为1个），进一步缩小了数据过滤范围，提升效率。

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