postgresql - 为什么一个多WHERE子句的SQL SELECT语句在每个子句中有两个条件，其中一个条件具有相同的值需要很长时间？

Question

我在 Postgres 9.1 数据库上运行了两个不同的 SQL 查询:

SELECT device_id, country FROM devices WHERE
(device_id = '97c179bd' AND country = 'US') OR
(device_id = 'bf5f50c6' AND country = 'US') OR
....
(device_id = '0e66c04d' AND country = 'US')

运行 12 秒(3620 个子句由 OR 分隔)

和

SELECT device_id, country FROM devices WHERE
(device_id = '97c179bd' AND country = 'US') OR
(device_id = 'bf5f50c6' AND country = 'US') OR
....
(device_id = '0e66c04d' AND country = 'US') OR
(device_id = '0e66c04d' AND country = 'different')

运行 0.6 秒(3620 个子句由 OR 分隔)

在第一个中，每个子句中的国家/地区条件都相同。在第二个中，我在最后一个子句中将国家/地区切换为“不同”。

第一个 select 语句需要 12 秒才能运行，第二个 select 语句需要 0.6 秒才能运行。

在第一个查询中，CPU 在 12 秒的几乎所有时间里都固定在 100%，没有任何磁盘读取，这表明可能是解析器花费了这么长时间。第二个查询不会发生这种情况。

我运行了 EXPLAIN ANALYZE，得到了关于如何分解这两个查询的完全相同的结果。

这是怎么回事？为什么在每个 WHERE 子句语句中第二个条件相同会导致更长的查询时间？

编辑:

来自第一个查询的 EXPLAIN ANALYZE 的前几行:

设备上的位图堆扫描(成本=18807.49..52584.74 行=3564 宽度=39)(实际时间=73.994..78.994 行=3620 循环=1)

重新检查条件:(((device_id = '97c179bd'::text) AND (country = 'US'::text)) OR ((device_id = 'bf5f50c6'::text) AND (country = 'US': :text)) OR ((device_id = '3b05d35a'::text) AND (country = 'US'::text)) OR ((device_id = 'c6684bc0'::text) AND (country = 'US'::text )) 或 ((device_id = '0e66c04d'::text) AND (country = 'US'::text))

来自第二个查询的 EXPLAIN ANALYZE 的前几行:

设备上的位图堆扫描(成本=18806.59..85317.68 行=3563 宽度=39)(实际时间=74.737..79.769 行=3619 循环=1)

重新检查条件:(((device_id = '97c179bd'::text) AND (country = 'US'::text)) OR ((device_id = 'bf5f50c6'::text) AND (country = 'US': :text)) OR ((device_id = '3b05d35a'::text) AND (country = 'US'::text)) OR ((device_id = 'c6684bc0'::text) AND (country = 'US'::text )) 或 ((device_id = '0e66c04d'::text) AND (country = 'US'::text))

编辑 2:

这是两个 EXPLAIN ANALYZE 结果:

https://dl.dropbox.com/u/4747107/explain/query1slow.htm

https://dl.dropbox.com/u/4747107/explain/query2fast.htm

Answer 1

虽然没有解释性能差异，但解决此问题的最佳方法是重构您的查询，使其不使用超过 3000 个 OR 子句。这太可怕了。

代替:

SELECT device_id, country FROM devices WHERE
(device_id = '97c179bd' AND country = 'US') OR
(device_id = 'bf5f50c6' AND country = 'US') OR
....
(device_id = '0e66c04d' AND country = 'US')

写:

SELECT d.device_id, d.country
FROM devices d 
INNER JOIN (VALUES 
            ('97c179bd','US'),
            ('bf5f50c6','US'),
            ('0e66c04d','US')
) v(device_id,country) USING (device_id,country);

演示设置:

create table devices (device_id text, country text, primary key (device_id,country));

insert into devices values 
        ('97c179bd','US'),
        ('bf5f50c6','US'),
        ('0e66c04d','US'),('0e66c04d','different');

演示输出:

regress=>     SELECT d.device_id, d.country
    FROM devices d 
    INNER JOIN (VALUES 
                ('97c179bd','US'),
                ('bf5f50c6','US'),
                ('0e66c04d','US')
    ) v(device_id,country) USING (device_id,country);
 device_id | country 
-----------+---------
 97c179bd  | US
 bf5f50c6  | US
 0e66c04d  | US
(3 rows)

对于较大的值列表，可能值得创建一个临时表并INSERTing 或COPYing 到其中，而不是使用内联VALUES 列表。对于真正庞大的数据集，您可能会受益于在 device_id,country 上创建唯一索引。