升级到9.6版本后，使用postgresql IN查询出现奇怪的性能问题。

Question

我们有一个数据库，目前在AWS RDS上运行PostgreSQL 9.5.4版本，我们正在尝试升级到9.6.6版本。升级后我们遇到了奇怪的性能下降问题，即使我们认为已经成功将所有的postgres设置复制到RDS参数组中，下面的查询似乎是一个“smoking gun”，但我们并不真正理解其中的原因。

在我们的9.5.4实例中，下面的所有查询都很快（正如您所期望的那样，因为uuid和account_id列是索引的）。

production=> \timing 
Timing is on.
production=> SELECT uuid FROM address WHERE account_id = 'Demo' LIMIT 1;
                 uuid                 
--------------------------------------
 4c52c1fb-a344-4ea4-90f8-2f7f9b2cdce0
(1 row)

Time: 3.015 ms
production=> SELECT uuid FROM address WHERE uuid IN ('4c52c1fb-a344-4ea4-90f8-2f7f9b2cdce0');
                 uuid                 
--------------------------------------
 4c52c1fb-a344-4ea4-90f8-2f7f9b2cdce0
(1 row)

Time: 0.886 ms
production=>  SELECT uuid FROM address WHERE uuid IN (SELECT uuid FROM address WHERE account_id = 'Demo' LIMIT 1);
                 uuid                 
--------------------------------------
 4c52c1fb-a344-4ea4-90f8-2f7f9b2cdce0
(1 row)

Time: 2.431 ms

一旦我们将数据库升级到9.6.6，前两个查询仍然很快，但最后一个查询变得非常慢：

production=> \timing 
Timing is on.
production=> SELECT uuid FROM address WHERE account_id = 'Demo' LIMIT 1;
                 uuid                 
--------------------------------------
 747b4b38-81f3-487e-8202-06c964e7e9f8
(1 row)

Time: 0.732 ms
production=> SELECT uuid FROM address WHERE uuid IN ('747b4b38-81f3-487e-8202-06c964e7e9f8');
                 uuid                 
--------------------------------------
 747b4b38-81f3-487e-8202-06c964e7e9f8
(1 row)

Time: 0.715 ms
production=> SELECT uuid FROM address WHERE uuid IN (SELECT uuid FROM address WHERE account_id = 'Demo' LIMIT 1);
                 uuid                 
--------------------------------------
 747b4b38-81f3-487e-8202-06c964e7e9f8
(1 row)

Time: 6676.759 ms

在9.6.6版本的系统中，查询规划器没有提供太多提示（至少我没看到）：

production=> EXPLAIN SELECT uuid FROM address WHERE uuid IN (SELECT uuid FROM address WHERE account_id = 'Demo' LIMIT 1);
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=5.23..13.27 rows=1 width=16)
   ->  HashAggregate  (cost=4.67..4.68 rows=1 width=16)
         Group Key: address_1.uuid
         ->  Limit  (cost=0.56..4.66 rows=1 width=16)
               ->  Index Scan using address_account_id on address address_1  (cost=0.56..725.46 rows=177 width=16)
                     Index Cond: ((account_id)::text = 'Demo'::text)
   ->  Index Only Scan using address_pkey1 on address  (cost=0.56..8.58 rows=1 width=16)
         Index Cond: (uuid = address_1.uuid)
(8 rows)

此外，对两个服务器运行标准的pgbench测试，实际表明9.6.6版本的服务器在每秒事务数方面优于9.5.4版本的服务器，因此我认为那里没有什么奇怪的硬件问题。

不知道是否有人对第三个查询性能下降的原因有任何想法？

- glenc

Answer 1

事实证明，这是因为在9.5 -> 9.6升级后，您需要对整个数据库进行ANALYZE以使查询规划器再次运转。

- glenc