postgresql 优化之--不会使用索引

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: --原始表结构如下:postgres=# \d+ postgres_s1_log Table "public.
--原始表结构如下:
postgres=# \d+ postgres_s1_log
                                       Table "public.postgres_s1_log"
         Column         |              Type              | Modifiers | Storage  | Stats target | Description 
------------------------+--------------------------------+-----------+----------+--------------+-------------
 log_time               | timestamp(3) without time zone |           | plain    |              | 
 user_name              | text                           |           | extended |              | 
 database_name          | text                           |           | extended |              | 
 process_id             | integer                        |           | plain    |              | 
 connection_from        | text                           |           | extended |              | 
 session_id             | text                           | not null  | extended |              | 
 session_line_num       | bigint                         | not null  | plain    |              | 
 command_tag            | text                           |           | extended |              | 
 session_start_time     | timestamp without time zone    |           | plain    |              | 
 virtual_transaction_id | text                           |           | extended |              | 
 transaction_id         | bigint                         |           | plain    |              | 
 error_severity         | text                           |           | extended |              | 
 sql_state_code         | text                           |           | extended |              | 
 message                | text                           |           | extended |              | 
 detail                 | text                           |           | extended |              | 
 hint                   | text                           |           | extended |              | 
 internal_query         | text                           |           | extended |              | 
 internal_query_pos     | integer                        |           | plain    |              | 
 context                | text                           |           | extended |              | 
 query                  | text                           |           | extended |              | 
 query_pos              | integer                        |           | plain    |              | 
 location               | text                           |           | extended |              | 
 application_name       | text                           |           | extended |              | 
Indexes:
    "pk_postgres_s1_log" PRIMARY KEY, btree (session_id, session_line_num)
    "idx_error_severity" btree (error_severity)
Has OIDs: no




--原始sql
postgres=# explain 
postgres-# select t1.database_name 数据库名,t1.user_name 用户名,  cast(substring(t1.message from  position(':' in t1.message)+2  for  position('ms' in t1.message)-position(':' in t1.message)-2)  as float) 执行时间,t2.message  语句 ,t2.detail  参数  ,t1.log_time 执行时间,left(t1.connection_from, position(':' in t1.connection_from)-1)   来源IP  
postgres-# from postgres_s1_log t1,postgres_s1_log t2 where   t1.message  like 'duration:%'  and   t1.user_name<>'postgres'  and  t1.database_name='dev' and cast(substring(t1.message from  position(':' in t1.message)+2  for  position('ms' in t1.message)-position(':' in t1.message)-2)  as float)>5000
postgres-# and t1.session_id=t2.session_id and t1.session_line_num=t2.session_line_num+1  order by  3 desc limit 500;
                                                                                                                                                               QUERY PLAN                                                                                                     
                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------
 Limit  (cost=820519.37..820520.62 rows=500 width=280)
   ->  Sort  (cost=820519.37..820640.72 rows=48538 width=280)
         Sort Key: (("substring"(t1.message, ("position"(t1.message, ':'::text) + 2), (("position"(t1.message, 'ms'::text) - "position"(t1.message, ':'::text)) - 2)))::double precision)
         ->  Merge Join  (cost=797111.93..818100.77 rows=48538 width=280)
               Merge Cond: ((t2.session_id = t1.session_id) AND (((t2.session_line_num + 1)) = t1.session_line_num))
               ->  Sort  (cost=609126.84..612760.01 rows=1453271 width=188)
                     Sort Key: t2.session_id, ((t2.session_line_num + 1))
                     ->  Seq Scan on postgres_s1_log t2  (cost=0.00..58028.71 rows=1453271 width=188)
               ->  Materialize  (cost=187985.10..189790.94 rows=361169 width=136)
                     ->  Sort  (cost=187985.10..188888.02 rows=361169 width=136)
                           Sort Key: t1.session_id, t1.session_line_num
                           ->  Seq Scan on postgres_s1_log t1  (cost=0.00..105260.02 rows=361169 width=136)
                                 Filter: ((message ~~ 'duration:%'::text) AND (user_name <> 'postgres'::text) AND (database_name = 'dev'::text) AND (("substring"(message, ("position"(message, ':'::text) + 2), (("position"(message, 'ms'::text) - "position"(message, ':'::
text)) - 2)))::double precision > 5000::double precision))
--阅读sql可知,该sql是一个自边接,对t1执行主要的过滤,对t2而言依据t1的结果执行过滤取值,且有组合索引 btree (session_id, session_line_num) ,但由于t1.session_line_num=t2.session_line_num+1 至使t2最多只能使用一部分索引




--修改条件 t1.session_line_num=t2.session_line_num+1 为t1.session_line_num -1 =t2.session_line_num,发现t2还是没有使用索引,
--但由于t1与t2进行join时并没有全部使用过滤条件,注意 Seq Scan on postgres_s1_log t1  (cost=0.00..105260.02 rows=361169 width=136),可发现t1与t2 join,时,pg估计有36万行数据估执行hash join也是正常的,但此时执行计划已发生变化,
postgres=# explain 
postgres-# select t1.database_name 数据库名,t1.user_name 用户名,  cast(substring(t1.message from  position(':' in t1.message)+2  for  position('ms' in t1.message)-position(':' in t1.message)-2)  as float) 执行时间,t2.message  语句 ,t2.detail  参数  ,t1.log_time 执行时间,left(t1.connection_from, position(':' in t1.connection_from)-1)   来源IP  
postgres-# from postgres_s1_log t1,postgres_s1_log t2 where   t1.message  like 'duration:%'  and   t1.user_name<>'postgres'  and  t1.database_name='dev' and cast(substring(t1.message from  position(':' in t1.message)+2  for  position('ms' in t1.message)-position(':' in t1.message)-2)  as float)>5000
postgres-# and t1.session_id=t2.session_id and t1.session_line_num -1 =t2.session_line_num   order by  3 desc limit 500;
                                                                                                                                                         QUERY PLAN                                                                                                           
                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------
 Limit  (cost=807502.65..807503.90 rows=500 width=280)
   ->  Sort  (cost=807502.65..807623.99 rows=48538 width=280)
         Sort Key: (("substring"(t1.message, ("position"(t1.message, ':'::text) + 2), (("position"(t1.message, 'ms'::text) - "position"(t1.message, ':'::text)) - 2)))::double precision)
         ->  Hash Join  (cost=118146.77..805084.05 rows=48538 width=280)
               Hash Cond: ((t1.session_id = t2.session_id) AND ((t1.session_line_num - 1) = t2.session_line_num))
               ->  Seq Scan on postgres_s1_log t1  (cost=0.00..105260.02 rows=361169 width=136)
                     Filter: ((message ~~ 'duration:%'::text) AND (user_name <> 'postgres'::text) AND (database_name = 'dev'::text) AND (("substring"(message, ("position"(message, ':'::text) + 2), (("position"(message, 'ms'::text) - "position"(message, ':'::text)) - 2))
)::double precision > 5000::double precision))
               ->  Hash  (cost=58028.71..58028.71 rows=1453271 width=188)
                     ->  Seq Scan on postgres_s1_log t2  (cost=0.00..58028.71 rows=1453271 width=188)

					 
					 

--既然已知t1,最多只是返回500条数据,则完全可以让t1先执行过滤再与t2进行join,且t2可以使用索引
postgres=# explain
postgres-# with w1 as (
postgres(# select database_name 数据库名,user_name 用户名,  cast(substring(message from  position(':' in message)+2  for  position('ms' in message)-position(':' in message)-2)  as float) 执行用时  ,log_time 执行时间,left(connection_from, position(':' in connection_from)-1) 来源IP,session_id, session_line_num 
postgres(# from postgres_s1_log where   message  like 'duration:%'  and   user_name<>'postgres'  and  database_name='dev'
postgres(# and  cast(substring(message from  position(':' in message)+2  for  position('ms' in message)-position(':' in message)-2)  as float)>5000  order by  3 desc limit 500)
postgres-# select 数据库名,用户名,执行用时,t2.message  语句 ,t2.detail  参数,执行时间,来源IP from w1, postgres_s1_log t2 where w1.session_id=t2.session_id and w1.session_line_num -1 =t2.session_line_num;
                                                                                                                                                          QUERY PLAN                                                                                                          
                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------
 Nested Loop  (cost=134093.43..138292.75 rows=13 width=278)
   CTE w1
     ->  Limit  (cost=134091.75..134093.00 rows=500 width=136)
           ->  Sort  (cost=134091.75..134994.67 rows=361169 width=136)
                 Sort Key: (("substring"(postgres_s1_log.message, ("position"(postgres_s1_log.message, ':'::text) + 2), (("position"(postgres_s1_log.message, 'ms'::text) - "position"(postgres_s1_log.message, ':'::text)) - 2)))::double precision)
                 ->  Seq Scan on postgres_s1_log  (cost=0.00..116095.09 rows=361169 width=136)
                       Filter: ((message ~~ 'duration:%'::text) AND (user_name <> 'postgres'::text) AND (database_name = 'dev'::text) AND (("substring"(message, ("position"(message, ':'::text) + 2), (("position"(message, 'ms'::text) - "position"(message, ':'::text)) - 2
)))::double precision > 5000::double precision))
   ->  CTE Scan on w1  (cost=0.00..10.00 rows=500 width=152)
   ->  Index Scan using pk_postgres_s1_log on postgres_s1_log t2  (cost=0.43..8.37 rows=1 width=188)
         Index Cond: ((session_id = w1.session_id) AND (session_line_num = (w1.session_line_num - 1)))
(10 rows)


--能过原始的cost=807503.90到最后优化的cost=138292.75,性能大概提高了6倍,此时可发现系统的消耗主要在t1,后续需要继续对其进行优化

					 

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
12月前
|
SQL 关系型数据库 测试技术
沉浸式学习PostgreSQL|PolarDB 20: 学习成为数据库大师级别的优化技能
在上一个实验《沉浸式学习PostgreSQL|PolarDB 19: 体验最流行的开源企业ERP软件 odoo》 中, 学习了如何部署odoo和polardb|pg. 由于ODOO是非常复杂的ERP软件, 对于关系数据库的挑战也非常大, 所以通过odoo业务可以更快速提升同学的数据库优化能力, 发现业务对数据库的使用问题(如索引、事务对锁的运用逻辑问题), 数据库的代码缺陷, 参数或环境配置问题, 系统瓶颈等.
928 1
|
2月前
|
监控 关系型数据库 数据库
PostgreSQL的索引优化策略?
【8月更文挑战第26天】PostgreSQL的索引优化策略?
57 1
|
16天前
|
缓存 关系型数据库 数据库
如何优化 PostgreSQL 数据库性能?
如何优化 PostgreSQL 数据库性能?
18 2
|
2月前
|
监控 关系型数据库 数据库
如何优化PostgreSQL的性能?
【8月更文挑战第4天】如何优化PostgreSQL的性能?
110 7
|
2月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
133 0
|
2月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL索引维护看完这篇就够了
PostgreSQL索引维护看完这篇就够了
168 0
|
5月前
|
存储 JSON 关系型数据库
PostgreSQL Json应用场景介绍和Shared Detoast优化
PostgreSQL Json应用场景介绍和Shared Detoast优化
|
5月前
|
弹性计算 关系型数据库 数据库
开源PostgreSQL在倚天ECS上的最佳优化实践
本文基于倚天ECS硬件平台,以自顶向下的方式从上层应用、到基础软件,再到底层芯片硬件,通过应用与芯片的硬件特性的亲和性分析,实现PostgreSQL与倚天芯片软硬协同的深度优化,充分使能倚天硬件性能,帮助开源PostgreSQL应用实现性能提升。
|
存储 关系型数据库 数据库
PostgreSQL技术大讲堂 - 第28讲:索引内部结构
从零开始学PostgreSQL技术大讲堂 - 第28讲:索引内部结构
655 2
|
12月前
|
关系型数据库 Go 数据库
《提高查询速度:PostgreSQL索引实用指南》
《提高查询速度:PostgreSQL索引实用指南》
526 0
下一篇
无影云桌面