PostgreSQL的最佳查询和3列上的复杂索引，其中2列具有静态值，第3列使用运算符IN

mbyulnm0 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(2)|浏览(112)

有一个复合索引表（col1，col2，col3）。这张表里有很多数据。想构建查询，例如WHERE col1 = 2 AND col2 = 12 AND col3 IN（1，2，3，...，40）是否有一种方法可以完全使用索引（3列）？
当我努力的时候

SELECT *
FROM table t
WHERE t.col1 = 2
    AND t.col2 = 12
    AND t.col3 IN (1, 2, 3, ..., 40)

字符串
Postgres planner对（col1，col2）进行索引扫描，然后使用SeqScan逐个过滤400k行，col3 IN (1, 2, 3, ..., 40)
如果我尝试

SELECT *
FROM table t
WHERE (col1, col2, col3) IN VALUES (2, 12, 1), (2, 12, 2), (2, 12, 3), ... ,(2, 12, 40)

型
它给出错误：
临时文件大小超过temp_file_limit
所以它工作缓慢。有没有一种方法可以让postgres对3列使用复合索引？

postgresql

来源：https://stackoverflow.com/questions/76780700/optimal-query-to-postgresql-and-complex-index-on-3-columns-when-2-columns-have

2条答案

按热度按时间

6gpjuf901#

根据您的评论，看起来我们可以通过(col1, col2, col3) = (arg1, arg2, arg3)上的显式连接强制使用索引。
我不知道你是如何调用这个查询的，但是如果从一个允许通过数据库驱动程序传递int[]类型的宿主语言调用，我的查询将看起来像这样：

with invars as (
  select 2 as c1val, 12 as c2val,
         array[1, 2, 3, 4, 5, 6, 40] as c3vals  
), search_tuples as (
  select i.c1val, i.c2val, u.c3val
    from invars i
         cross join lateral unnest(i.c3vals) as u(c3val)
)
select t.*
  from search_tuples s
       join table1 t 
    on (t.col1, t.col2, t.col3) = (s.c1val, s.c2val, s.c3val);

字符串
A working fiddle with random test records and explain

赞(0）回复(0）举报 2023-08-04

hivapdat2#

您可以尝试将col3可能的值加载到一个真正的表中，然后将查询重写为以下内容：

SELECT t1.*
FROM yourTable t1
WHERE t1.col1 = 2 AND
      t1.col2 = 12 AND
      EXISTS (
          SELECT 1
          FROM table2 t2
          WHERE t2.col3 = t1.col3
      );

字符串
假设table2具有以下结构：

table2:
col3
1
2
3
...
40

型
table1可以使用(col1, col2, col3)上的索引。还应该在table2 (col3)上放置一个索引，以确保快速查找。

赞(0）回复(0）举报 2023-08-04

我来回答

PostgreSQL的最佳查询和3列上的复杂索引，其中2列具有静态值，第3列使用运算符IN

2条答案

相关问题

热门标签

最新问答