时间戳

首页 > 解决方案 > 使用多个 IN 优化查询

postgresql - 使用多个 IN 优化查询

问题描述

我有一个这样的查询：

SELECT * FROM table
WHERE department='param1' AND type='param2' AND product='param3'
AND product_code IN (10-30 alphanumerics) AND unit_code IN (10+ numerics)
AND first_name || last_name IN (10-20 names)
AND sale_id LIKE ANY(list of regex string)

运行时间太高了，所以我被要求优化它。

参数列表因不同用户的代码列而异。每个用户提供他们的代码列表，然后循环访问产品。product 曾经也是一个 IN 子句列表，但它被拆分了。

我尝试过的事情

通过在（部门、类型和产品）上添加索引，我能够获得 4 倍的改进。当前运行时间是 product 的一些值只需要 2-3 秒，而另一些需要 30s。

尝试创建一个预先连接的 first_name || 列 last_name，但运行时改进太小，不值得。

有什么方法可以提高其他子句的性能，例如“IN”子句或 LIKE ANY 子句？

标签： postgresqlquery-optimizationdatabase-administration

解决方案

根据我替换大型 IN 列表的经验，使用 JOIN 到 VALUES 子句通常可以提高性能。

所以而不是：

SELECT * 
FROM table
WHERE department='param1' 
  AND type='param2' 
  AND product='param3'
  AND product_code IN (10-30 alphanumerics)

利用：

SELECT * 
FROM table t
   JOIN ( values (1),(2),(3) ) as x(code) on x.code = t.product_code
WHERE department='param1' 
  AND type='param2' 
  AND product='param3'

values ()但是您必须确保列表中没有任何重复项

连接也是错误的，因为连接的值是不同的，然后单独比较每个值，例如('alexander', 'son') would be treated identical to （'alex'，'anderson'）`

你应该使用：

and (first_name, last_name) in ( ('fname1', 'lname1'), ('fname2', 'lname2'))

这也可以写成一个连接

SELECT * 
FROM table t
  JOIN ( values (1),(2),(3) ) as x(code) on x.code = t.product_code
  JOIN ( 
     values ('fname1', 'lname1'), ('fname2', 'lname2') 
  ) as n(fname, lname) on (n.fname, n.lname) = (t.first_name, t.last_name)
WHERE department='param1' 
  AND type='param2' 
  AND product='param3'

推荐阅读