logo标签列表

使用子查询的PostgreSQL IN操作符性能差

performancepostgresqlrdbmsoperator-keyword
39
为什么在使用子查询时，“IN”运算符会如此缓慢？
select * 
from view1 
where id in (1,2,3,4,5,6,7,8,9,10) 
order by somedata;

执行时间为9毫秒。

select * 
from view1 
where id in (select ext_id 
             from aggregate_table 
             order by somedata limit 10) 
order by somedata;

第二个查询需要25000毫秒才能执行完，似乎是在视图(view1)上执行了顺序扫描而不是使用子查询返回的主键进行索引扫描，而第一个查询则是使用了主键索引扫描。

子查询select ext_id from aggregate_table order by somedata limit 10只需要0.1毫秒就可以执行完。

所以第二个查询缓慢的原因是因为在包含三个UNION和每个UNION中有约三个JOIN的视图view1上执行了顺序扫描。第一个UNION包含大约100万行，其他UNION则要少得多。与一些有10万行数据的表进行连接。虽然这不是那么相关，但我只是想了解IN操作符的行为。

我要完成的目标是使用子查询的结果(一组主键)仅选择来自复杂视图(view1)的数据。

我也不能使用

select v1.* 
from view1 v1, 
     aggregate_table at 
where v1.id = at.ext_id 
order by at.somedata 
limit 10

由于我不想根据 somedata 对大型连接进行排序。我只想通过主键从视图中选择10个结果，然后仅对这些结果进行排序。

问题是，为什么使用 IN 运算符显式列出这些键时执行速度快，而使用返回完全相同的键集的快速子查询时速度如此缓慢？

按要求 EXPLAIN ANALYZE

第一个查询 - select * from view1 where id in (1,2,3,4,5,6,7,8,9,10) order by somedata;

    Sort  (cost=348.480..348.550 rows=30 width=943) (actual time=14.385..14.399 rows=10 loops=1)
    Sort Key: "india".three
    Sort Method:  quicksort  Memory: 30kB
  ->  Append  (cost=47.650..347.440 rows=30 width=334) (actual time=11.528..14.275 rows=10 loops=1)
        ->  Subquery Scan "*SELECT* 1"  (cost=47.650..172.110 rows=10 width=496) (actual time=11.526..12.301 rows=10 loops=1)
              ->  Nested Loop  (cost=47.650..172.010 rows=10 width=496) (actual time=11.520..12.268 rows=10 loops=1)
                    ->  Hash Join  (cost=47.650..87.710 rows=10 width=371) (actual time=11.054..11.461 rows=10 loops=1)
                            Hash Cond: (hotel.alpha_five = juliet_xray.alpha_five)
                          ->  Bitmap Heap Scan on sierra hotel  (cost=42.890..82.800 rows=10 width=345) (actual time=10.835..11.203 rows=10 loops=1)
                                  Recheck Cond: (four = ANY ('quebec'::integer[]))
                                ->  Bitmap Index Scan on seven  (cost=0.000..42.890 rows=10 width=0) (actual time=0.194..0.194 rows=10 loops=1)
                                        Index Cond: (four = ANY ('quebec'::integer[]))
                          ->  Hash  (cost=4.340..4.340 rows=34 width=30) (actual time=0.184..0.184 rows=34 loops=1)
                                ->  Seq Scan on six juliet_xray  (cost=0.000..4.340 rows=34 width=30) (actual time=0.029..0.124 rows=34 loops=1)
                    ->  Index Scan using charlie on juliet_two zulu  (cost=0.000..8.390 rows=1 width=129) (actual time=0.065..0.067 rows=1 loops=10)
                            Index Cond: (zulu.four = hotel.victor_whiskey)
        ->  Subquery Scan "*SELECT* 2"  (cost=4.760..97.420 rows=10 width=366) (actual time=0.168..0.168 rows=0 loops=1)
              ->  Hash Join  (cost=4.760..97.320 rows=10 width=366) (actual time=0.165..0.165 rows=0 loops=1)
                      Hash Cond: (alpha_xray.alpha_five = juliet_xray2.alpha_five)
                    ->  Nested Loop  (cost=0.000..92.390 rows=10 width=340) (actual time=0.162..0.162 rows=0 loops=1)
                          ->  Seq Scan on lima_echo alpha_xray  (cost=0.000..8.340 rows=10 width=216) (actual time=0.159..0.159 rows=0 loops=1)
                                  Filter: (four = ANY ('quebec'::integer[]))
                          ->  Index Scan using charlie on juliet_two xray  (cost=0.000..8.390 rows=1 width=128) (never executed)
                                  Index Cond: (zulu2.four = alpha_xray.victor_whiskey)
                    ->  Hash  (cost=4.340..4.340 rows=34 width=30) (never executed)
                          ->  Seq Scan on six uniform  (cost=0.000..4.340 rows=34 width=30) (never executed)
        ->  Subquery Scan "*SELECT* 3"  (cost=43.350..77.910 rows=10 width=141) (actual time=1.775..1.775 rows=0 loops=1)
              ->  Hash Join  (cost=43.350..77.810 rows=10 width=141) (actual time=1.771..1.771 rows=0 loops=1)
                      Hash Cond: (golf.alpha_five = juliet_xray3.alpha_five)
                    ->  Bitmap Heap Scan on lima_golf golf  (cost=38.590..72.910 rows=10 width=115) (actual time=0.110..0.110 rows=0 loops=1)
                            Recheck Cond: (four = ANY ('quebec'::integer[]))
                          ->  Bitmap Index Scan on victor_hotel  (cost=0.000..38.590 rows=10 width=0) (actual time=0.105..0.105 rows=0 loops=1)
                                  Index Cond: (four = ANY ('quebec'::integer[]))
                    ->  Hash  (cost=4.340..4.340 rows=34 width=30) (actual time=0.118..0.118 rows=34 loops=1)
                          ->  Seq Scan on six victor_kilo  (cost=0.000..4.340 rows=34 width=30) (actual time=0.007..0.063 rows=34 loops=1)
 Total runtime: 14.728 ms

第二个查询 - select * from view1 where id in (select ext_id from aggregate_table order by somedata limit 10) order by somedata;

Sort  (cost=254515.780..254654.090 rows=55325 width=943) (actual time=24687.475..24687.488 rows=10 loops=1)
    Sort Key: "five".xray_alpha
    Sort Method:  quicksort  Memory: 30kB
  ->  Hash Semi Join  (cost=54300.820..250157.370 rows=55325 width=943) (actual time=11921.783..24687.308 rows=10 loops=1)
          Hash Cond: ("five".lima = "delta_echo".lima)
        ->  Append  (cost=54298.270..235569.720 rows=1106504 width=494) (actual time=3412.453..23091.938 rows=1106503 loops=1)
              ->  Subquery Scan "*SELECT* 1"  (cost=54298.270..234227.250 rows=1100622 width=496) (actual time=3412.450..20234.122 rows=1100622 loops=1)
                    ->  Hash Join  (cost=54298.270..223221.030 rows=1100622 width=496) (actual time=3412.445..17078.021 rows=1100622 loops=1)
                            Hash Cond: (three_victor.xray_hotel = delta_yankee.xray_hotel)
                          ->  Hash Join  (cost=54293.500..180567.160 rows=1100622 width=470) (actual time=3412.251..12108.676 rows=1100622 loops=1)
                                  Hash Cond: (three_victor.tango_three = quebec_seven.lima)
                                ->  Seq Scan on india three_victor  (cost=0.000..104261.220 rows=1100622 width=345) (actual time=0.015..3437.722 rows=1100622 loops=1)
                                ->  Hash  (cost=44613.780..44613.780 rows=774378 width=129) (actual time=3412.031..3412.031 rows=774603 loops=1)
                                      ->  Seq Scan on oscar quebec_seven  (cost=0.000..44613.780 rows=774378 width=129) (actual time=4.142..1964.036 rows=774603 loops=1)
                          ->  Hash  (cost=4.340..4.340 rows=34 width=30) (actual time=0.149..0.149 rows=34 loops=1)
                                ->  Seq Scan on alpha_kilo delta_yankee  (cost=0.000..4.340 rows=34 width=30) (actual time=0.017..0.095 rows=34 loops=1)
              ->  Subquery Scan "*SELECT* 2"  (cost=4.760..884.690 rows=104 width=366) (actual time=7.846..10.161 rows=104 loops=1)
                    ->  Hash Join  (cost=4.760..883.650 rows=104 width=366) (actual time=7.837..9.804 rows=104 loops=1)
                            Hash Cond: (foxtrot.xray_hotel = delta_yankee2.xray_hotel)
                          ->  Nested Loop  (cost=0.000..877.200 rows=104 width=340) (actual time=7.573..9.156 rows=104 loops=1)
                                ->  Seq Scan on four_india foxtrot  (cost=0.000..7.040 rows=104 width=216) (actual time=0.081..0.311 rows=104 loops=1)
                                ->  Index Scan using three_delta on oscar alpha_victor  (cost=0.000..8.350 rows=1 width=128) (actual time=0.077..0.078 rows=1 loops=104)
                                        Index Cond: (quebec_seven2.lima = foxtrot.tango_three)
                          ->  Hash  (cost=4.340..4.340 rows=34 width=30) (actual time=0.216..0.216 rows=34 loops=1)
                                ->  Seq Scan on alpha_kilo quebec_foxtrot  (cost=0.000..4.340 rows=34 width=30) (actual time=0.035..0.153 rows=34 loops=1)
              ->  Subquery Scan "*SELECT* 3"  (cost=4.760..457.770 rows=5778 width=141) (actual time=0.264..58.353 rows=5777 loops=1)
                    ->  Hash Join  (cost=4.760..399.990 rows=5778 width=141) (actual time=0.253..39.062 rows=5777 loops=1)
                            Hash Cond: (four_uniform.xray_hotel = delta_yankee3.xray_hotel)
                          ->  Seq Scan on whiskey four_uniform  (cost=0.000..315.780 rows=5778 width=115) (actual time=0.112..15.759 rows=5778 loops=1)
                          ->  Hash  (cost=4.340..4.340 rows=34 width=30) (actual time=0.117..0.117 rows=34 loops=1)
                                ->  Seq Scan on alpha_kilo golf  (cost=0.000..4.340 rows=34 width=30) (actual time=0.005..0.059 rows=34 loops=1)
        ->  Hash  (cost=2.430..2.430 rows=10 width=4) (actual time=0.303..0.303 rows=10 loops=1)
              ->  Subquery Scan "ANY_subquery"  (cost=0.000..2.430 rows=10 width=4) (actual time=0.092..0.284 rows=10 loops=1)
                    ->  Limit  (cost=0.000..2.330 rows=10 width=68) (actual time=0.089..0.252 rows=10 loops=1)
                          ->  Index Scan using tango_seven on zulu romeo  (cost=0.000..257535.070 rows=1106504 width=68) (actual time=0.087..0.227 rows=10 loops=1)
 Total runtime: 24687.975 ms
1
你能展示一下 EXPLAIN ANALYZE SELECT ... 吗？也许可以使用 depesz。 - MatheusOl
我会尝试将子查询结果放入临时表中，然后使用IN（select id from temptable）进行操作。不同之处在于“limit”子句可能会导致优化器为table1中的每一行执行子选择。顺便说一句，这似乎与@Clodoaldo所建议的方法是相同的。 - rootkit
尝试使用以下代码：create temporary table aggregate_table_tmp as select ext_id from aggregate_table order by somedata limit 10，并在子查询中使用它：select * from table1 where id in (select ext_id from aggregate_table_tmp) order by somedata，但是没有成功。执行时间仍然是25000毫秒。 - Snifff
1
"table1...是一个包含三个连接的视图" - 关于视图和连接 - 我注意到（尤其是在SQL服务器上），将JOIN更改为LEFT JOIN或删除ORDER BY可能会对性能产生巨大影响（特别是在视图上，而不一定是与之相关的查询），值得尝试。 "view1"不是一个更少误导性的名称吗？ - Bernhard Barker
你能展示一下视图定义和实际查询吗？我认为这会对理解你的解释结果有很大帮助。 - MatheusOl
显示剩余7条评论
3个回答
54

看来我终于找到了解决方案：

select * 
  from view1 
  where view1.id = ANY(
                       (select array(select ext_id 
                                     from aggregate_table 
                                     order by somedata limit 10)
                       )::integer[]
                      ) 
  order by view1.somedata;

在阐述了@Dukeling的想法之后：

我怀疑where id in (1,2,3,4,5,6,7,8,9,10)可以被优化，而where id in (select ...)不能被优化，原因是(1,2,3,4,5,6,7,8,9,10)是一个常量表达式，而select不是。

并且在更快的查询计划中定位它们。

Recheck Cond: (id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
Index Cond: (id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))

这个比问题中第一个查询还要快，大约1.2毫秒，现在使用的是：
Recheck Cond: (id = ANY ($1))
Index Cond: (id = ANY ($1))

和位图扫描在计划中。

19
使用 ARRAY 的技巧可以指示PG在子查询中使用索引。顺便说一句，上述的 ANY 子句可以简化为这个 ANY(array(<your select query>))。 - Blue Smith
我目前还没有大数据可以做实验，但为什么要使用 select array(select ...)，而不是 @BlueSmith 建议的 array(select ...)？此外，::integer[] 有什么区别吗？例如，如果我有字符串值，是否需要转换任何类型以获得更快的性能？ - akostadinov
那么这个解决方案的时间尺度差异是多少？ - Sunny Patel
24687.975毫秒 vs 1.2毫秒。假设有可用的索引等。但我相信这在更近期的PG版本中已经得到解决，不再是问题，查询规划器会处理。 - Snifff
3
我认为可以对where id in (1,2,3,4,5,6,7,8,9,10)进行优化，而where id in (select ...)则不能，原因是(1,2,3,4,5,6,7,8,9,10)是一个常量表达式，而select不是。
你觉得怎么样？
WITH myCTE AS
(
  SELECT ext_id
  FROM aggregate_table
  ORDER BY somedata
  LIMIT 10
)
SELECT *
FROM myCTE
LEFT JOIN table1
  ON myCTE.ext_id = table1.id
ORDER BY somedata
同@Clodoaldo的变体，24000毫秒。 - Snifff
@Snifff 已更改为 LEFT JOIN，可能会有所不同。最终结论似乎是 PostgreSQL 在优化方面做得很糟糕，我想看看 MySQL 或 SQL Server 在相同数据上的表现。 - Bernhard Barker
4
“LEFT JOIN”确实有所不同——时间长达65000毫秒:( - Snifff
仍然进行顺序扫描，太遗憾了 :( - PirateApp
0

这与LINQ中的解决方案类似：

连接查询的执行计划可能很复杂，因此

c= a.Select(...).Select( ...)

替换为

b= a.Select( ...).ToArray(); c=b.Select(...)

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接