PostgreSQL中ARRAY子查询易变慢，主因是动态子查询返回的数组无法被优化器静态推导，导致GIN索引失效、退化为顺序扫描；正确做法是提前物化为常量或直接展开数组。
为什么ARRAY子查询容易变慢
PostgreSQL里用ARRAY类型做子查询，常见写法是WHERE id IN (SELECT ARRAY[...])或WHERE tags @> ARRAY['a','b']嵌套在子查询中。问题不在数组本身，而在于子查询是否可下推、是否触发重复计算、是否绕过GIN索引。比如写成SELECT * FROM items WHERE tags @> (SELECT ARRAY_AGG(tag) FROM user_prefs WHERE user_id = 1)，PG无法预知子查询结果长度和内容，可能放弃使用@>的索引路径，退化为顺序扫描。
避免在子查询中动态构造数组用于@>匹配
用@>判断包含关系时，右侧必须是常量数组或能被优化器静态推导的表达式。动态子查询返回的数组值无法参与索引选择，即使你建了GIN(tags)索引也无效。
- ❌ 错误写法：
WHERE tags @> (SELECT ARRAY['tag1','tag2'] FROM some_config LIMIT 1)—— 子查询未内联，优化器不信任其稳定性 - ✅ 正确做法：把子查询提前物化，用
WITH绑定为常量，或直接展开：WHERE tags @> ARRAY['tag1','tag2'] - ⚠️ 注意：如果数组元素来自另一张表且数量可控（JOIN替代子查询，让优化器走
Hash Semi Join+Index Only Scan
用UNNEST + JOIN替代IN (子查询返回数组)
当子查询返回一个TEXT[]字段，你想查“某字段值是否在这个数组里”，别用IN (SELECT tags FROM ...)——这会强制PG对每行做数组展开+逐个比对，无索引可言。
- ❌ 慢：
WHERE name IN (SELECT tags FROM metadata WHERE scope = 'global')（假设tags是TEXT[]） - ✅ 快：
FROM items i JOIN LATERAL (SELECT UNNEST(m.tags) AS tag FROM metadata m WHERE m.scope = 'global') t ON i.name = t.tag - ? 原理：LATERAL让
UNNEST按需展开，配合name上的B-tree索引，可快速定位匹配项；若name基数高，再加WHERE i.name IS NOT NULL避免空值干扰
GIN索引失效的两个隐蔽场景
即使你建了CREATE INDEX idx_items_tags ON items USING GIN(tags)，以下情况仍会跳过索引：
- 子查询中用了函数包裹数组，如
WHERE tags @> UPPER(ARRAY['a'])——UPPER破坏了操作符可索引性 - 数组元素含
NULL，例如ARRAY['a', NULL]传入子查询，@>行为未定义，PG保守起见放弃索引扫描 - 查询条件混用
@>和&&但未对齐索引策略：GIN索引默认只加速@>和&&，不加速（被包含），除非显式指定<code>USING gin(tags gin__array_ops)
最易被忽略的是：数组字段本身允许NULL，而GIN索引默认不存NULL条目——这意味着WHERE tags IS NULL永远无法走这个索引，得单独建IS NULL专用索引或改用COALESCE(tags, '{}')统一兜底。
