使用带有列表的 IN 的 SQL 查询非常慢 [英] SQL query using IN with a list very slow

问题描述

有没有办法优化这样的查询:

Is there a way to optimize a query done like this:

SELECT count(*) FROM mytable 
WHERE 
indexed_field IN ('v1', 'v2', ..., 'v2000')
AND NOT regular_field='something'
AND other_regular_field='something_else';

这个查询有效".问题是它非常慢(1分钟+).我认为使用定义的列表使用 IN 表达式会很好，但事实证明并不是那么好.

This query "works". The problem is that it is extremely slow (1min+). I thought that using the IN expression would be fine using a defined list, but it is turning out to not be that fine.

我使用的是 Postgresql 9.6.

I'm using Postgresql 9.6.

我的表有 310K.

查询说明:

QUERY PLAN
Aggregate  (cost=396158.70..396158.71 rows=1 width=8) (actual time=8630.558..8630.559 rows=1 loops=1)
  ->  Seq Scan on mytable  (cost=0.00..396156.77 rows=772 width=0) (actual time=7853.840..8630.478 rows=916 loops=1)
        Filter: (((non_indexed_field)::text <> 'value1'::text) AND ((non_indexed_field)::text = 'value2'::text) AND ((indexed_field)::text = ANY ('{lots and lots of values....}'::text[])))
        Rows Removed by Filter: 306768
Planning time: 2.543 ms

Execution time: 8630.770 ms

分析，缓冲区:

Aggregate  (cost=396158.70..396158.71 rows=1 width=8) (actual time=9278.560..9278.560 rows=1 loops=1)
   Buffers: shared hit=14244
   ->  Seq Scan on mytable  (cost=0.00..396156.77 rows=772 width=0) (actual time=8584.520..9278.431 rows=916 loops=1)
         Filter: (((non_indexed_field)::text <> 'value1'::text) AND ((non_indexed_field)::text = 'value2'::text) AND ((indexed_field)::text = ANY ('{lots and lots of values}'::text[])))
         Rows Removed by Filter: 306768
         Buffers: shared hit=14244
 Planning time: 1.293 ms
 Execution time: 9278.646 ms
(8 rows)

推荐答案

经常加入 VALUES 子句会更有效:

Very often joining to a a VALUES clause is more efficient:

SELECT count(*) 
FROM mytable 
  JOIN (
     values ('v1'), ('v2'), (...), ('v2000')
  ) as lookup(value) on lookup.value = mytable.some_column
WHERE NOT other_column = 'something'
AND another_column = 'something_else';

注意列表"的格式.在 values 子句中，每个值都需要用括号括起来.('v1'), ('v2'), ... 而不是 ('v1', 'v2', ...)

Pay attention to the format of the "list". In the values clause, each value needs to be enclosed in parentheses. ('v1'), ('v2'), ... instead of ('v1', 'v2', ...)

在线示例:http://rextester.com/UUWVG71217

请注意，如果与值进行比较的列确实是数字(例如整数)，则您应该不要使用单引号指定值，例如值 (1),(2),(3),...(2000)

Note that if the column you compare the values to really is a number (e.g. integer), you should not specify the values using single quotes, e.g. values (1),(2),(3),...(2000)

这篇关于使用带有列表的 IN 的 SQL 查询非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！