PostgreSQL 多查询条件,多个索引的选择算法与问题诊断方法

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

标签

PostgreSQL , 多列条件 , 多索引 , 单列索引 , 复合索引 , 联合索引 , 优化器 , 评估 , 行评估 , 成本


背景

当一个SQL中涉及多个条件,并且多个条件有多种索引可选时,数据库优化器是如何选择使用哪个索引的?

例如

有一张表,有2个字段,单列一个索引,双列一个复合索引.

建表。  
postgres=# create table tbl(id int, gid int);  
CREATE TABLE  
  
插入1000万记录,其中ID唯一,GID只有10个值。  
postgres=# insert into tbl select generate_series(1,10000000), random()*9 ;  
INSERT 0 10000000  
  
创建两个索引。  
postgres=# create index idx1 on tbl(id);  
CREATE INDEX  
postgres=# create index idx2 on tbl(gid,id);  
CREATE INDEX  

下面三条SQL,会如何选择使用哪个索引呢?

select * from tbl where id in (1,2,3,4,5,6,7,8,9,10) and gid=123;  
  
select * from tbl where id in (1,2,3,4,5,6,7,8,9,10) and gid=1;  
  
select * from tbl where id in (1,2,3,4,5,6,7,8,9,10);  

问题思考

人为选择

这三条QUERY,实际上有三重含义:

1、gid=123的行根本不存在。

如果让你来选索引,你肯定会选复合索引,马上就能定位到数据不存在扫描最少的BLOCK。

select * from tbl where id in (1,2,3,4,5,6,7,8,9,10) and gid=123;  

2、gid=1存在,同时id里面的条件也存在。

如果让你来选索引,应该也是选择复合索引,因为精确定位到了所有的行。

当然如果id in里面很多记录不存在,那么你可能就会选择id单列索引,因为这个索引本身更小,可能扫描更少的BLOCK。

select * from tbl where id in (1,2,3,4,5,6,7,8,9,10) and gid=1;  

3、只有id的条件。

此时,肯定选单列索引了。

select * from tbl where id in (1,2,3,4,5,6,7,8,9,10);  

实际情况如何呢?

1、数据库执行计划与预期一致

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where id in (1,2,3,4,5,6,7,8,9,10) and gid=123;  
                                                      QUERY PLAN                                                        
----------------------------------------------------------------------------------------------------------------------  
 Index Only Scan using idx2 on public.tbl  (cost=0.43..2.46 rows=1 width=8) (actual time=0.037..0.037 rows=0 loops=1)  
   Output: id, gid  
   Index Cond: (tbl.gid = 123)  
   Filter: (tbl.id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))  
   Heap Fetches: 0  
   Buffers: shared hit=3  
 Planning time: 0.829 ms  
 Execution time: 0.086 ms  
(8 rows)  

2、与预期一致

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where id in (1,2,3,4,5,6,7,8,9,10) and gid=1;  
                                                      QUERY PLAN                                                         
-----------------------------------------------------------------------------------------------------------------------  
 Index Only Scan using idx2 on public.tbl  (cost=0.43..15.46 rows=1 width=8) (actual time=0.026..0.037 rows=2 loops=1)  
   Output: id, gid  
   Index Cond: ((tbl.gid = 1) AND (tbl.id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[])))  
   Heap Fetches: 2  
   Buffers: shared hit=31  
 Planning time: 0.121 ms  
 Execution time: 0.058 ms  
(7 rows)  

3、与预期一致

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where id in (1,2,3,4,5,6,7,8,9,10);  
                                                     QUERY PLAN                                                       
--------------------------------------------------------------------------------------------------------------------  
 Index Scan using idx1 on public.tbl  (cost=0.43..15.52 rows=10 width=8) (actual time=0.021..0.035 rows=10 loops=1)  
   Output: id, gid  
   Index Cond: (tbl.id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))  
   Buffers: shared hit=31  
 Planning time: 0.104 ms  
 Execution time: 0.055 ms  
(6 rows)  

问题升华

数据库生成执行计划靠的是统计信息,如果统计信息不准确,那么执行计划必然不准确。

例如我们人为关闭TBL的自动统计信息收集,然后写入一批新的数据。

postgres=# alter table tbl set (autovacuum_enabled =off);  
ALTER TABLE  
postgres=# insert into tbl select generate_series(1,10000000), 100;  
INSERT 0 10000000  

这个数据的特点是GID=100,在原有的统计信息中,gid=100的行是不存在的,所以下面的SQL优化器显然做出了错误的决定。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where id in (1,2,3,4,5,6,7,8,9,10) and gid=100;  
                                                        QUERY PLAN                                                          
--------------------------------------------------------------------------------------------------------------------------  
 Index Only Scan using idx2 on public.tbl  (cost=0.44..2.46 rows=1 width=8) (actual time=0.030..2051.851 rows=10 loops=1)  
   Output: id, gid  
   Index Cond: (tbl.gid = 100)  
   Filter: (tbl.id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))  
   Rows Removed by Filter: 9999990  
   Heap Fetches: 10000000  
   Buffers: shared hit=71574  
 Planning time: 0.130 ms  
 Execution time: 2051.900 ms  
(9 rows)  

更新统计信息后,执行计划就准确了。

postgres=# analyze tbl;  
ANALYZE  
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where id in (1,2,3,4,5,6,7,8,9,10) and gid=100;  
                                                       QUERY PLAN                                                          
-------------------------------------------------------------------------------------------------------------------------  
 Index Only Scan using idx2 on public.tbl  (cost=0.44..20.57 rows=10 width=8) (actual time=0.027..0.043 rows=10 loops=1)  
   Output: id, gid  
   Index Cond: ((tbl.gid = 100) AND (tbl.id = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[])))  
   Heap Fetches: 10  
   Buffers: shared hit=31  
 Planning time: 0.212 ms  
 Execution time: 0.067 ms  
(7 rows)  

如何自动收集统计信息

开启autovacuum , track_counts即可。

有几个微调参数,决定了什么时候扫描是否需要收集统计信息,以及当前表的变化量。

track_counts = on  
  
#------------------------------------------------------------------------------  
# AUTOVACUUM PARAMETERS  
#------------------------------------------------------------------------------  
  
autovacuum = on                 # Enable autovacuum subprocess?  'on'  
                                        # requires track_counts to also be on.  
#log_autovacuum_min_duration = -1       # -1 disables, 0 logs all actions and  
                                        # their durations, > 0 logs only  
                                        # actions running at least this number  
                                        # of milliseconds.  
#autovacuum_max_workers = 3             # max number of autovacuum subprocesses  
                                        # (change requires restart)  
autovacuum_naptime = 3s         # time between autovacuum runs  
#autovacuum_vacuum_threshold = 50       # min number of row updates before  
                                        # vacuum  
#autovacuum_analyze_threshold = 50      # min number of row updates before  
                                        # analyze  
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum  
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze  
#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum  
                                        # (change requires restart)  
#autovacuum_multixact_freeze_max_age = 400000000        # maximum multixact age  
                                        # before forced vacuum  
                                        # (change requires restart)  
autovacuum_vacuum_cost_delay = 0ms      # default vacuum cost delay for  
                                        # autovacuum, in milliseconds;  
                                        # -1 means use vacuum_cost_delay  
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for  
                                        # autovacuum, -1 means use  
                                        # vacuum_cost_limit  

PostgreSQL优化器是支持CBO与遗传算法

《数据库优化器原理 - 如何治疗选择综合症》

评估每个条件过滤多少行

《PostgreSQL pg_stats used to estimate top N freps values and explain rows》

统计信息解读

《PostgreSQL pg_stat_ pg_statio_ 统计信息(scan,read,fetch,hit)源码解读》

《PostgreSQL 统计信息pg_statistic格式及导入导出dump_stat - 兼容Oracle》

升华-多列统计信息

《PostgreSQL 10 黑科技 - 自定义统计信息》

其他因统计信息不准导致的性能问题

《Greenplum 统计信息收集参数 - 暨统计信息不准引入的broadcast motion一例》

其他参考文献

《PostgreSQL 10 黑科技 - 自定义统计信息》

《数据库优化器原理 - 如何治疗选择综合症》

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

《PostgreSQL 9种索引的原理和应用场景》

《Greenplum 统计信息收集参数 - 暨统计信息不准引入的broadcast motion一例》

《PostgreSQL pg_stat_ pg_statio_ 统计信息(scan,read,fetch,hit)源码解读》

《PostgreSQL 统计信息pg_statistic格式及导入导出dump_stat - 兼容Oracle》

《PostgreSQL pg_stats used to estimate top N freps values and explain rows》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
17天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
|
25天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
49 9
|
27天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
60 3
|
1月前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(5)作者——LJS[含MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
MySQL DQL基本查询:select;简单、排序、分组、聚合、分组、分页、INSERT INTO SELECT / FROM查询结合精例等详解步骤及常见报错问题所对应的解决方法
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
176 1
|
2月前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
1月前
|
SQL 关系型数据库 数据库
PostgreSQL性能飙升的秘密:这几个调优技巧让你的数据库查询速度翻倍!
【10月更文挑战第25天】本文介绍了几种有效提升 PostgreSQL 数据库查询效率的方法,包括索引优化、查询优化、配置优化和硬件优化。通过合理设计索引、编写高效 SQL 查询、调整配置参数和选择合适硬件,可以显著提高数据库性能。
180 1
|
1月前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
46 1
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
76 0
|
2月前
|
存储 SQL 关系型数据库
MySQL 给查询结果增列并自定义列数据
MySQL 给查询结果增列并自定义列数据
553 2

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版