Postgres Materialize导致删除查询性能不佳

Question

我需要在PostgreSQL 9.0.4上运行一个删除查询。我发现它一直在运行，直到在子选择查询中达到524289行。

例如，524288没有使用物化视图，成本看起来相当不错：

explain DELETE FROM table1 WHERE pointLevel = 0 AND userID NOT IN
(SELECT userID FROM table2 fetch first 524288 rows only);
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete  (cost=13549.49..17840.67 rows=21 width=6)
   ->  Index Scan using jslps_userid_nopt on table1  (cost=13549.49..17840.67 rows=21 width=6)
         Filter: ((NOT (hashed SubPlan 1)) AND (pointlevel = 0))
         SubPlan 1
           ->  Limit  (cost=0.00..12238.77 rows=524288 width=8)
                 ->  Seq Scan on table2  (cost=0.00..17677.92 rows=757292 width=8)
(6 rows)

然而，当我点击524289时，物化视图就开始发挥作用，删除查询的成本就要高得多：

explain DELETE FROM table1 WHERE pointLevel = 0 AND userID NOT IN
(SELECT userID FROM table2 fetch first 524289 rows only);

  QUERY PLAN

-----------------------------------------------------------------------------------------------------------  
Delete  (cost=0.00..386910.33 rows=21 width=6)
    ->  Index Scan using jslps_userid_nopt on table1  (cost=0.00..386910.33 rows=21 width=6)
         Filter: ((pointlevel = 0) AND (NOT (SubPlan 1)))
         SubPlan 1
           ->  Materialize  (cost=0.00..16909.24 rows=524289 width=8)
                 ->  Limit  (cost=0.00..12238.79 rows=524289 width=8)
                       ->  Seq Scan on table2  (cost=0.00..17677.92 rows=757292 width=8) (7 rows)

我通过在子选择查询中使用JOIN来解决这个问题：

SELECT s.userid 
FROM table1 s 
LEFT JOIN table2 p ON s.userid=p.userid
WHERE p.userid IS NULL AND s.pointlevel=0

然而，我仍然有兴趣理解为什么materialize会大幅降低性能。

Answer 1

我的猜测是在row=524289内存缓冲区被填满，所以子查询必须在磁盘上具体化。因此所需时间急剧增加。

在这里，您可以阅读有关配置内存缓冲区的更多信息：http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html
如果你玩work\u mem，你会看到查询行为的不同。

然而，在子查询中使用连接是加快查询速度的更好方法，因为您限制了源本身的行数，而不是简单地选择第一个XYZ行，然后执行检查。