关于postgres 11订单的性能问题
关于postgres 11订单的性能问题
提问于 2020-08-25 05:34:33
我们最近从postgres 10.6迁移到postgres 11.7的每个人，我们的一些查询都面临一些性能问题，特别是这个特定的查询：
SELECT credits.ref
FROM credits
WHERE ((credits.contentRef = 'local-test-10:contents-episode-421254'
OR credits.contentRef = 'local-test-10:contents-show-156789'
OR credits.contentRef = 'local-test-10:contents-episode-227267'
OR credits.contentRef = 'local-test-10:contents-episode-388789'
OR credits.contentRef = 'local-test-10:contents-episode-870245'
OR credits.contentRef = 'local-test-10:contents-episode-280712')
AND credits.owner = 'local-test-10')
ORDER BY credits.id ASC
LIMIT 100表credits有几个索引，其中一个索引用于列contentref：
"credits_contentref_idx" btree (contentref NULLS FIRST, id)在postgres 10中，规划者将该索引和性能结合在一起：
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit (cost=3500.92..3501.17 rows=100 width=50) (actual time=0.049..0.049 rows=0 loops=1) |
| -> Sort (cost=3500.92..3501.99 rows=429 width=50) (actual time=0.048..0.048 rows=0 loops=1) |
| Sort Key: id |
| Sort Method: quicksort Memory: 25kB |
| -> Bitmap Heap Scan on credits (cost=58.60..3484.52 rows=429 width=50) (actual time=0.043..0.043 rows=0 loops=1) |
| Recheck Cond: ((contentref = 'local-test-10:contents-episode-421254'::text) OR (contentref = 'local-test-10:contents-show-156789'::text) OR (contentref = 'local-test-10:contents-episode-227267'::text) OR (contentref = 'local-test-10:contents-episode-388789'::text) OR (contentref = 'local-test-10:contents-episode-870245'::text) OR (contentref = 'local-test-10:contents-episode-280712'::text)) |
| Filter: (owner = 'local-test-10'::text) |
| -> BitmapOr (cost=58.60..58.60 rows=881 width=0) (actual time=0.042..0.042 rows=0 loops=1) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..9.66 rows=147 width=0) (actual time=0.016..0.016 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-episode-421254'::text) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..9.66 rows=147 width=0) (actual time=0.006..0.006 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-show-156789'::text) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..9.66 rows=147 width=0) (actual time=0.006..0.006 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-episode-227267'::text) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..9.66 rows=147 width=0) (actual time=0.004..0.004 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-episode-388789'::text) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..9.66 rows=147 width=0) (actual time=0.005..0.005 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-episode-870245'::text) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..9.66 rows=147 width=0) (actual time=0.004..0.004 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-episode-280712'::text) |
| Planning time: 0.246 ms |
| Execution time: 0.086 ms |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+但是使用postgres 11.7，规划者没有使用该索引，而且性能很差
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit (cost=0.56..7522.67 rows=100 width=50) (actual time=4159.270..4159.270 rows=0 loops=1) |
| -> Index Scan using credits_owner_idx on credits (cost=0.56..910176.43 rows=12100 width=50) (actual time=4159.268..4159.268 rows=0 loops=1) |
| Index Cond: (owner = 'local-test-10'::text) |
| Filter: ((contentref = 'local-test-10:contents-episode-421254'::text) OR (contentref = 'local-test-10:contents-show-156789'::text) OR (contentref = 'local-test-10:contents-episode-227267'::text) OR (contentref = 'local-test-10:contents-episode-388789'::text) OR (contentref = 'local-test-10:contents-episode-870245'::text) OR (contentref = 'local-test-10:contents-episode-280712'::text)) |
| Rows Removed by Filter: 3318877 |
| Planning Time: 0.278 ms |
| Execution Time: 4159.746 ms |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+如果我删除Postgres 11中的顺序，计划者将索引和性能再次结合起来。
SELECT credits.ref
FROM credits
WHERE ((credits.contentRef = 'local-test-10:contents-episode-421254'
OR credits.contentRef = 'local-test-10:contents-show-156789'
OR credits.contentRef = 'local-test-10:contents-episode-227267'
OR credits.contentRef = 'local-test-10:contents-episode-388789'
OR credits.contentRef = 'local-test-10:contents-episode-870245'
OR credits.contentRef = 'local-test-10:contents-episode-280712')
AND credits.owner = 'local-test-10')
LIMIT 100+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit (cost=766.23..1109.72 rows=100 width=34) (actual time=0.049..0.049 rows=0 loops=1) |
| -> Bitmap Heap Scan on credits (cost=766.23..42328.05 rows=12100 width=34) (actual time=0.048..0.048 rows=0 loops=1) |
| Recheck Cond: ((contentref = 'local-test-10:contents-episode-421254'::text) OR (contentref = 'local-test-10:contents-show-156789'::text) OR (contentref = 'local-test-10:contents-episode-227267'::text) OR (contentref = 'local-test-10:contents-episode-388789'::text) OR (contentref = 'local-test-10:contents-episode-870245'::text) OR (contentref = 'local-test-10:contents-episode-280712'::text)) |
| Filter: (owner = 'local-test-10'::text) |
| -> BitmapOr (cost=766.23..766.23 rows=12902 width=0) (actual time=0.044..0.044 rows=0 loops=1) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..124.68 rows=2150 width=0) (actual time=0.017..0.017 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-episode-421254'::text) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..124.68 rows=2150 width=0) (actual time=0.006..0.006 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-show-156789'::text) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..124.68 rows=2150 width=0) (actual time=0.006..0.006 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-episode-227267'::text) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..124.68 rows=2150 width=0) (actual time=0.005..0.005 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-episode-388789'::text) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..124.68 rows=2150 width=0) (actual time=0.005..0.005 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-episode-870245'::text) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..124.68 rows=2150 width=0) (actual time=0.004..0.004 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-episode-280712'::text) |
| Planning Time: 0.235 ms |
| Execution Time: 0.139 ms |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+到目前为止，我还不如Postgres 10，我试过：
- 相反，使用OR使用IN或数组和任何->都不会改进
- 在查询中使用的3列上创建索引(contentref、owner、id) ->没有改进
我没有提高查询性能的想法，我不知道为什么规划者不像早期版本那样组合索引
有什么想法吗？
如果我减少了查询中"OR“的数量，则计划器将合并索引：
SELECT credits.ref
FROM credits
WHERE ((credits.contentRef = 'local-test-10:contents-episode-421254'
OR credits.contentRef = 'local-test-10:contents-show-156789')
AND credits.owner = 'local-test-10')
ORDER BY credits.id ASC
LIMIT 100+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|---------------------------------------------------------------------------------------------------------------------------------------------------------|
| Limit (cost=16796.49..16796.74 rows=100 width=50) (actual time=0.027..0.027 rows=0 loops=1) |
| -> Sort (cost=16796.49..16807.26 rows=4307 width=50) (actual time=0.027..0.027 rows=0 loops=1) |
| Sort Key: id |
| Sort Method: quicksort Memory: 25kB |
| -> Bitmap Heap Scan on credits (cost=269.82..16631.88 rows=4307 width=50) (actual time=0.021..0.021 rows=0 loops=1) |
| Recheck Cond: ((contentref = 'local-test-10:contents-episode-421254'::text) OR (contentref = 'local-test-10:contents-show-156789'::text)) |
| Filter: (owner = 'local-test-10'::text) |
| -> BitmapOr (cost=269.82..269.82 rows=4608 width=0) (actual time=0.017..0.017 rows=0 loops=1) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..133.84 rows=2304 width=0) (actual time=0.010..0.010 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-episode-421254'::text) |
| -> Bitmap Index Scan on credits_contentref_idx (cost=0.00..133.84 rows=2304 width=0) (actual time=0.006..0.006 rows=0 loops=1) |
| Index Cond: (contentref = 'local-test-10:contents-show-156789'::text) |
| Planning Time: 0.256 ms |
| Execution Time: 0.079 ms |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+我检查了包含两个or的计划器组合了索引，但是如果我加上3个或更多的or，那么规划者就不会组合它
回答 1
Database Administration用户
发布于 2020-08-27 04:51:56
好了，我终于解决了这个问题，我刚刚创建了两个新的统计数据，并修改了该列contentref的统计信息：
ALTER TABLE credits ALTER contentref SET STATISTICS 10000
CREATE STATISTICS credits_contentref_owner (dependencies) ON contentref, owner FROM credits;
CREATE STATISTICS credits_contentref_ref (dependencies) ON contentref, ref FROM credits;基本上，我已经将postgres配置为：
- 使用列contentref的更多不同值来估计要检查的行
- 列contentref与所有者之间存在相关性，因此了解contentref将知道所有者的值。
- 列contentref与ref之间存在相关性，因此了解contentref将知道ref的值。
通过这样做，查询的计划更准确，因此查询运行得更好。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/274282
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号