postgresql - 9.6 升级后 postgresql IN 查询出现奇怪的性能问题
问题描述
我们有一个数据库,目前在 AWS RDS 上运行 postgresql 9.5.4,我们正在尝试将其升级到运行 9.6.6。升级后我们遇到了奇怪的性能下降,即使(我们认为)成功地将所有 postgres 设置复制到 RDS 参数组之后,下面的查询似乎是一把“确凿的证据”,尽管我们没有真的明白。
在我们的 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 盒子,所以我不认为那里发生了一些奇怪的硬件问题。
好奇是否有人对第三个查询的奇怪性能下降可能来自哪里有任何想法?
解决方案
原来这是因为在 9.5 -> 9.6 升级之后,您需要ANALYZE
整个数据库才能让查询计划器再次嗡嗡作响。
推荐阅读
- c# - REST API Best practice for handling junction data
- java - 我无法使用 Spring Security 登录
- java - Java derby表Java代码中的多个更新,for循环仅最后一次更新工作
- c# - 数据库 c# if (reader[""].count > 2)
- node.js - Mongoose connection printing out both console.log when mongo server not running
- java - 在 Java Swing 中嵌入 Youtube 视频
- javascript - 使用 javascript 作为快速视图引擎
- javascript - Javascript file totally not loading in WP theme when setting all 5 parameters in wp_enqueue_script()
- go - Go中的链码-Hyperledger v 1.0-返回的参数太多
- laravel - 请问我怎样才能让我的视图在 laravel 中显示内容