1. 简介
PostgreSQL 是一种开源关系型数据库管理系统，强调 SQL 标准的兼容性和扩展性。在日常的开发工作中，SELECT ... IN被广泛地用来进行多值查询。然而，当 IN 后面的值非常多时，查询效率会大大降低。本文将探讨 PostgreSQL 中 SELECT ... IN 的效率问题，及如何优化。
2. SELECT ... IN 的效率问题
在 PostgreSQL 中，使用 SELECT ... IN 进行多值查询的效率非常依赖于被查询的表的大小。当被查询的表很大时，查询效率会大大降低。
下面是一个简单的例子。假设我们有两个表，一个是 users 表，一个是 orders 表。它们的结构如下：
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
price DECIMAL(10, 2)
);
我们将为 orders 表插入 1,000,000 条数据：
INSERT INTO users (name) VALUES
('Alice'), ('Bob'), ('Charlie'), ('Dave');
INSERT INTO orders (user_id, price)
SELECT
(random()*4)::integer + 1,
(random()*100)::numeric(10, 2)
FROM generate_series(1, 1000000);
现在，我们想查询购买了特定商品的用户。假设这个特定商品是以下几个：
('bread', 10.0),
('eggs', 20.0),
('milk', 30.0),
('cheese', 40.0)
我们可以使用下面的 SQL 语句进行查询：
SELECT DISTINCT user_id FROM orders
WHERE (product, price) IN (
('bread', 10.0),
('eggs', 20.0),
('milk', 30.0),
('cheese', 40.0)
);
然而，当 orders 表非常大时，这个查询语句的效率会很低，因为 PostgreSQL 可能会使用顺序扫描整个表，逐一比较每一行的数据，以确定哪些行满足条件。可以利用 Explain Analyze 来查看查询运行的详细信息，例如：
EXPLAIN ANALYZE
SELECT DISTINCT user_id FROM orders
WHERE (product, price) IN (
('bread', 10.0),
('eggs', 20.0),
('milk', 30.0),
('cheese', 40.0)
);
这个查询语句的运行时间取决于被查询的表的大小。
3. 如何优化
为了优化 SELECT ... IN 的效率问题，我们可以采用以下几种方法。
3.1 使用索引
为 orders 表中经常用来进行多值查询的字段创建索引，这样可以极大地提高查询效率。在上面的例子中，我们经常用到字段 product 和 price，我们可以为它们创建一个联合索引：
CREATE INDEX orders_product_price_idx ON orders (product, price);
注意：创建索引需要在表的大小和查询的方式之间做出权衡，在某些情况下可能不是最佳选择。
3.2 使用 EXISTS
我们可以使用 EXISTS 子查询来达到相同的效果，这种方法的效率通常比使用 IN 更高。
SELECT DISTINCT user_id FROM orders
WHERE EXISTS (
SELECT *
FROM (VALUES
('bread', 10.0),
('eggs', 20.0),
('milk', 30.0),
('cheese', 40.0)
) AS t(product, price)
WHERE orders.product = t.product AND orders.price = t.price
);
3.3 使用临时表
我们可以使用以下 SQL 语句来创建一个临时表，然后将要进行匹配的值插入到这个表中。最后，我们将使用 INNER JOIN 来将这个表和 orders 表进行关联，以达到相同的效果。
CREATE TEMPORARY TABLE temp_products (
product VARCHAR(255),
price DECIMAL(10, 2)
);
INSERT INTO temp_products (product, price)
VALUES ('bread', 10.0), ('eggs', 20.0), ('milk', 30.0), ('cheese', 40.0);
SELECT DISTINCT user_id FROM orders
INNER JOIN temp_products
ON orders.product = temp_products.product AND orders.price = temp_products.price;
4. 总结
当 PostgreSQL 中的 SELECT ... IN 查询语句应用到大表中时，其效率会大幅下降。为了优化查询，我们可以使用索引、使用 EXISTS 子查询或使用临时表等方式。选择哪种方式取决于被查询表的大小、被查询的字段、查询的频率等多个因素，需要根据实际情况进行权衡。