PostgreSQL含大量ULID的ANY()慢查询索引优化咨询

表结构 #

CREATE TABLE public.products 
(
    id character varying NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    name character varying NOT NULL,
);

id字段使用ULID，当前类型为无固定长度varchar，可修改列类型。

慢查询语句 #

SELECT "products".* 
FROM "products" 
WHERE "id" = ANY('{01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF11PB3N1Q9TXME6KW1B,01HQDMCF0YFZBAV5K4ZQ3495FR,01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF0WJ53MH0WKNR65CBX1,01HQDMCF0ZV7TYZWRR6RN5V4QT,01HQDMCF0YRF7CB3DSC6DSAY54,01HQDMCF0YFZBAV5K4ZQ3495FR,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF0YCJ8EFYCBKHWN4VQN,01HQDMCF0Z46AY42FC8FR953D8,01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF0ZBQ3A70GE8K41RW1V,01HQDMCF0WNYW4ZH5G0M8MDAQA,01HQDMCF0ZM8B6BJJK38PH7M2F,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF0WT7K7W5GBFF3HVXHE,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF0W24YQHH4EKN91S0JY,01HQDMCF0WT7K7W5GBFF3HVXHE,01HQDMCF0ZM8B6BJJK38PH7M2F,01HQDMCF0YEQR7NJJJW88XNTW9,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF0YRF7CB3DSC6DSAY54,01HQDMCF0WBY0YT5MR53KG9HS8,01HQDMCF0WBY0YT5MR53KG9HS8,01HQDMCF106M1667NNPTDBKQDB,01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF0YFZBAV5K4ZQ3495FR,01HQDMCF0YFZBAV5K4ZQ3495FR,01HQDMCF0ZMPTQ0GHG0V87W0J4,01HQDMCF0ZM8B6BJJK38PH7M2F,01HQDMCF0ZM8B6BJJK38PH7M2F,01HQDMCF0WBY0YT5MR53KG9HS8,01HQDMCF0WHNJ0P9BGVEFJE2JG,01HQDMCF109V71KW4MTFWVRTFR,...}'::text[]) 
LIMIT 37520 OFFSET 0

关键说明：ANY()子句中包含约20k个ID值，推测这是查询缓慢的原因。

EXPLAIN ANALYZE 结果 #

Limit  (cost=85.51..90.82 rows=154 width=172) (actual time=1.765..1.799 rows=138 loops=1)
  ->  Seq Scan on products  (cost=85.51..90.82 rows=154 width=172) (actual time=1.764..1.791 rows=138 loops=1)
        Filter: ((id)::text = ANY ('{01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF11PB3N1Q9TXME6KW1B,01HQDMCF0YFZBAV5K4ZQ3495FR,01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF0WJ53MH0WKNR65CBX1,01HQDMCF0ZV7TYZWRR6RN5V4QT,01HQDMCF0YRF7CB3DSC6DSAY54,01HQDMCF0YFZBAV5K4ZQ3495FR,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF0YCJ8EFYCBKHWN4VQN,01HQDMCF0Z46AY42FC8FR953D8,01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF0ZBQ3A70GE8K41RW1V,01HQDMCF0WNYW4ZH5G0M8MDAQA,01HQDMCF0ZM8B6BJJK38PH7M2F,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF0WT7K7W5GBFF3HVXHE,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF0W24YQHH4EKN91S0JY,01HQDMCF0WT7K7W5GBFF3HVXHE,01HQDMCF0ZM8B6BJJK38PH7M2F,01HQDMCF0YEQR7NJJJW88XNTW9,01HQDMCF11Z9VKEQXNZKEWXRDN,01HQDMCF0YRF7CB3DSC6DSAY54,01HQDMCF0WBY0YT5MR53KG9HS8,01HQDMCF0WBY0YT5MR53KG9HS8,01HQDMCF106M1667NNPTDBKQDB,01HQDMCF0S7QWQYBP2FW9HK8DS,01HQDMCF0YFZBAV5K4ZQ3495FR,01HQDMCF0YFZBAV5K4ZQ3495FR,01HQDMCF0ZMPTQ0GHG0V87W0J4,01HQDMCF0ZM8B6BJJK38PH7M2F,01HQDMCF0ZM8B6BJJK38PH7M2F,01HQDMCF0WBY0YT5MR53KG9HS8,01HQDMCF0WHNJ0P9BGVEFJE2JG,01HQDMCF109V71KW4MTFWVRTFR,...}'::text[]))
        Rows Removed by Filter: 16
Planning Time: 9.769 ms
Execution Time: 1.878 ms

已尝试的索引（均无效） #

• 普通BTREE索引：

CREATE INDEX product_id_idx ON products(id);

• HASH索引：

CREATE INDEX product_id_idx ON products USING HASH(id);

• text_pattern_ops的BTREE索引：

CREATE INDEX product_id_pattern_idx ON products USING btree (id text_pattern_ops);

优化方案 #

1. 修复隐式类型转换问题 #

从执行计划可见(id)::text = ANY (...)，PostgreSQL将varchar类型的id转为text后匹配，导致索引无法被调用。

解决方式：
将查询中的数组类型改为varchar[]，避免隐式转换：

SELECT "products".* 
FROM "products" 
WHERE "id" = ANY('{01HQDMCF0S7QWQYBP2FW9HK8DS,...}'::varchar[]) 
LIMIT 37520 OFFSET 0

或者直接去掉显式的::text[]，让PostgreSQL自动匹配varchar类型。

2. 改用固定长度类型存储ULID #

ULID是固定26字符的字符串，将id列改为char(26)类型，比varchar更高效，还能避免长度差异导致的索引匹配问题：

ALTER TABLE products ALTER COLUMN id TYPE char(26);

之后重建普通BTREE索引：

CREATE INDEX product_id_idx ON products(id);

3. 优化批量查询写法 #

当IN/ANY列表过长时，PostgreSQL可能选择全表扫描，改用临时表+JOIN的方式性能更优：

-- 创建临时表并导入ID列表
CREATE TEMP TABLE temp_ids (id char(26) NOT NULL);
COPY temp_ids FROM stdin;
01HQDMCF0S7QWQYBP2FW9HK8DS
01HQDMCF11PB3N1Q9TXME6KW1B
...
\.

-- 建立临时表索引
CREATE INDEX temp_ids_idx ON temp_ids(id);

-- 通过JOIN查询
SELECT p.*
FROM products p
JOIN temp_ids t ON p.id = t.id
LIMIT 37520 OFFSET 0;

这种方式对几万条ID的批量查询，性能远优于IN/ANY。

4. 强制使用索引（临时方案） #

若上述方法均不生效，可尝试引导优化器使用索引：

SELECT "products".* 
FROM "products" 
WHERE "id" = ANY('{...}'::varchar[]) 
LIMIT 37520 OFFSET 0
ORDER BY id; -- 排序会引导优化器使用BTREE索引

或者会话内临时禁用全表扫描（不建议全局设置）：

SET enable_seqscan = off;

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