Postgresql执行计划学习

最新推荐文章于 2023-12-28 09:35:44 发布
最新推荐文章于 2023-12-28 09:35:44 发布
阅读量635 收藏
点赞数
分类专栏： psql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Linzhongyilisha/article/details/78318069
版权 
Postgresql执行计划：
testdb2=# explain select * from test;
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on test  (cost=0.00..18.50 rows=850 width=68)
(1 row)
Seq scan :表示顺序扫描，即全表扫描；
cost=0.00..18.50: 第一个数字0.00表示启动的成本，也就是返回第一行需要多少cost值；第二行表示所有数据的成本，。
rows=850:表示返回850行。
width=68:表示每行平均宽度为68字节

cost描述一个sql执行的代价是多少，默认情况下，不同的操作其“cost”值如下：
顺序扫描一个数据块， cost 1
随机扫描一个数据块，cost 4
处理一个数据行的cpu,cost 0.01 
处理一个索引行的cpu,cost 0.005
每个操作符的cpu代价为 0.0025

testdb2=# explain select a.id,b.id, a.col1 ,b.col1 from testtab05 a join testtab6 b on a.id = b.id
;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Merge Join  (cost=176.34..303.67 rows=8064 width=72)
   Merge Cond: (a.id = b.id)
   ->  Sort  (cost=88.17..91.35 rows=1270 width=36)
         Sort Key: a.id
         ->  Seq Scan on testtab05 a  (cost=0.00..22.70 rows=1270 width=36)
   ->  Sort  (cost=88.17..91.35 rows=1270 width=36)
         Sort Key: b.id
         ->  Seq Scan on testtab6 b  (cost=0.00..22.70 rows=1270 width=36)
(8 rows)


testdb2=# explain select a.id,a.name,b.age from t_user a join student b on a.id = b.no;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Hash Join  (cost=22.82..129.36 rows=2451 width=86)
   Hash Cond: (b.no = a.id)
   ->  Seq Scan on student b  (cost=0.00..18.60 rows=860 width=8)
   ->  Hash  (cost=15.70..15.70 rows=570 width=82)
         ->  Seq Scan on t_user a  (cost=0.00..15.70 rows=570 width=82)
(5 rows)

testdb2=# explain analyze select a.id,a.name,b.age from t_user a join student b on a.id = b.no;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=22.82..129.36 rows=2451 width=86) (actual time=0.068..0.071 rows=4 loops=1)
   Hash Cond: (b.no = a.id)
   ->  Seq Scan on student b  (cost=0.00..18.60 rows=860 width=8) (actual time=0.042..0.043 rows=6 loops=1)
   ->  Hash  (cost=15.70..15.70 rows=570 width=82) (actual time=0.010..0.010 rows=3 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on t_user a  (cost=0.00..15.70 rows=570 width=82) (actual time=0.005..0.006 rows=3 loops=1)
 Planning time: 0.130 ms
 Execution time: 0.234 ms
(8 rows)

testdb2=# explain (analyze true,buffers true) select a.id,a.name,b.age from t_user a join student b on a.id = b.no;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=22.82..129.36 rows=2451 width=86) (actual time=0.019..0.020 rows=4 loops=1)
   Hash Cond: (b.no = a.id)
   Buffers: shared hit=2
   ->  Seq Scan on student b  (cost=0.00..18.60 rows=860 width=8) (actual time=0.006..0.006 rows=6 loops=1)
         Buffers: shared hit=1
   ->  Hash  (cost=15.70..15.70 rows=570 width=82) (actual time=0.006..0.006 rows=3 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=1
         ->  Seq Scan on t_user a  (cost=0.00..15.70 rows=570 width=82) (actual time=0.003..0.004 rows=3 loops=1)
               Buffers: shared hit=1
 Planning time: 0.064 ms
 Execution time: 0.038 ms
(12 rows)
shared hit:表示在共享内存中直接读到的块；

全表扫描：
testdb2=# explain select * from testtab05;
                          QUERY PLAN                          
--------------------------------------------------------------
 Seq Scan on testtab05  (cost=0.00..22.70 rows=1270 width=36)
(1 row)

索引扫描：
testdb2=# explain select * from  jtest01 where id = 11 ;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on jtest01  (cost=0.00..2584.00 rows=1 width=80)
   Filter: (id = 11)
(2 rows)

testdb2=# select count(*) from jtest01;
 count  
--------
 100000
(1 row)

testdb2=# 
testdb2=# create index idx_jtest01_id on jtest01(id);
CREATE INDEX
testdb2=# explain select * from  jtest01 where id = 11 ;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Index Scan using idx_jtest01_id on jtest01  (cost=0.29..8.31 rows=1 width=80)
   Index Cond: (id = 11)
(2 rows)

testdb2=#

位图扫描
把满足条件的行货块在内存中建一个位图，扫描完索引后，再根据位图到表的数据文件中把相应的数据读出来。如果走了两个索引，可以把两个索引形成的位图进行 and 或 or 计算，合并成一个位图，再到表的数据文件中读取数据。
当执行计划的结果行数很多时会进行这种扫描，如非等值查询、in子句或有多个条件可走不同的索引时。
以下方式并咩有走位图扫描。

testdb2=# explain select * from jtest01 where id > 50000;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Index Scan using idx_jtest01_id on jtest01  (cost=0.29..2091.93 rows=49808 width=80)
   Index Cond: (id > 50000)
(2 rows)

testdb2=# explain select * from jtest01 where id > 5000;
                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on jtest01  (cost=0.00..2584.00 rows=94967 width=80)
   Filter: (id > 5000)
(2 rows)

testdb2=# 
testdb2=# select count(*) from jtest01;
 count  
--------
 100000
(1 row)

testdb2=# explain select count(*) from jtest01;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Aggregate  (cost=2584.00..2584.01 rows=1 width=8)
   ->  Seq Scan on jtest01  (cost=0.00..2334.00 rows=100000 width=0)
(2 rows)

testdb2=# explain select count(*) from jtest01 where id >4000;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Aggregate  (cost=2823.83..2823.84 rows=1 width=8)
   ->  Seq Scan on jtest01  (cost=0.00..2584.00 rows=95933 width=0)
         Filter: (id > 4000)
(3 rows)

条件过滤：
条件过滤在执行计划中显示为 Filter
如果列上有索引，会走索引，不走过滤

testdb2=# explain select * from jtest01 where id in (select id from jtest02) and jdoc->>'name' like 'a%';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Hash Semi Join  (cost=4180.00..7425.88 rows=500 width=80)
   Hash Cond: (jtest01.id = jtest02.id)
   ->  Seq Scan on jtest01  (cost=0.00..2834.00 rows=500 width=80)
         Filter: ((jdoc ->> 'name'::text) ~~ 'a%'::text)
   ->  Hash  (cost=2539.00..2539.00 rows=100000 width=4)
         ->  Seq Scan on jtest02  (cost=0.00..2539.00 rows=100000 width=4)
(6 rows)

Nestloop Join（嵌套循环连接）
小表做驱动表，大表做被驱动表,大表上有建索引，连接字段上有建索引。小表数据一般小于10000

Hash Join 
优化器使用两个表中较小的表，并利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
适用于较小的表可以完全放于内存中的情况，这样总成本就是访问两个表的成本之和。
如果表很大，不能完全放入内存，优化器会将它分割成若干不同的分区，把不能放入内存的部分写入磁盘的临时段，此时要有较大的临时段以便提高io性能。
testdb2=# explain analyze select a.id,a.name,b.age from t_user a join student b on a.id = b.no;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=22.82..129.36 rows=2451 width=86) (actual time=0.068..0.071 rows=4 loops=1)
   Hash Cond: (b.no = a.id)
   ->  Seq Scan on student b  (cost=0.00..18.60 rows=860 width=8) (actual time=0.042..0.043 rows=6 loops=1)
   ->  Hash  (cost=15.70..15.70 rows=570 width=82) (actual time=0.010..0.010 rows=3 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on t_user a  (cost=0.00..15.70 rows=570 width=82) (actual time=0.005..0.006 rows=3 loops=1)
 Planning time: 0.130 ms
 Execution time: 0.234 ms
(8 rows)
先在较小的表t_user上建立散列表，然后在扫描较大的表 student 并探测散列表，找出与散列表匹配的行。


Merge Join 
一般散列连接比合并连接效果好，但如果源数据上有索引，或者结果被排序好，在执行排序合并连接时就不需要排序了，这时合并排序的性能会优于散列连接。
testdb2=# explain select a.id,b.id, a.col1 ,b.col1 from testtab05 a join testtab6 b on a.id = b.id
;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Merge Join  (cost=176.34..303.67 rows=8064 width=72)
   Merge Cond: (a.id = b.id)
   ->  Sort  (cost=88.17..91.35 rows=1270 width=36)
         Sort Key: a.id
         ->  Seq Scan on testtab05 a  (cost=0.00..22.70 rows=1270 width=36)
   ->  Sort  (cost=88.17..91.35 rows=1270 width=36)
         Sort Key: b.id
         ->  Seq Scan on testtab6 b  (cost=0.00..22.70 rows=1270 width=36)
(8 rows)
此时id上没有建索引， Sort Key:a.id,Sort key：b.id是对表中id字段进行排序。
建完索引后，则走了hash join
testdb2=# create index idx_id_testtab6 on testtab6(id);
CREATE INDEX
testdb2=# create index idx_id_testtab05 on testtab05(id);
CREATE INDEX
testdb2=# explain select a.id,b.id, a.col1 ,b.col1 from testtab05 a join testtab6 b on a.id = b.id
testdb2-# ;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Hash Join  (cost=1.07..2.18 rows=3 width=72)
   Hash Cond: (b.id = a.id)
   ->  Seq Scan on testtab6 b  (cost=0.00..1.06 rows=6 width=36)
   ->  Hash  (cost=1.03..1.03 rows=3 width=36)
         ->  Seq Scan on testtab05 a  (cost=0.00..1.03 rows=3 width=36)
(5 rows)


通常情况下，PostsgreSQL都不会走错的执行计划。Postgresql走错的执行的执行计划是统计信息收集不及时导致的，可通过频繁运行ANALYZE来解决这个问题，使用“ENABLE_”只是一个临时方法。

统计信息的收集
表和索引的行数、块数等统计信息记录再系统表pg_class中，其它的统计信息主要收集在系统表pg_statistic中。
1.统计信息收集器的配置项

2.SQL执行的统计信息输出

3.手工收集统计信息
手工收集统计信息的命令是analyze，此命令收集表的统计信息，然后把结果存在系统表pg_statistic里。
postgresql中，autovacuum守护进程是打开的，它自动分析表，并收集统计信息。当autovacuum关闭时，需要周期地，或在表的大部分内容变更后运行ANALYZE命令。
常用的策略：每天在数据库比较空闲的时候运行一次VACUUM和ANALYZE。
testdb2=# VACUUM;
VACUUM
testdb2=# ANALYZE;
ANALYZE

ANALYZE命令格式：
ANALYZE [VERBOSE] [table [(column [,...])]]
VERBOSE:显示处理的进度，及表的一些统计信息
table:要分析的表名，不指定则默认所有的表
column:要分析的特定字段，默认所有的字段。
如：ANALYZE test(id,col1);

siwluxuefeng
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
炼数成金深度学习PostgreSQL.txt
08-09
第十一课Postgresql引擎开发深入：执行优化器实现分析，增加兼容其它数据库存储过程功能。第十二课Postgresql引擎开发深入：数据库数据字典实现分析，扩展系统性能动态视图。第十三课发布数据库引擎：扩展并运行...
PostgreSQL执行计划介绍
u011868279的博客
08-31 4838
PostgreSQL执行计划介绍
postgresql执行计划
m0_61066945的博客
03-28 3734
用于测试分析、SQL调优
进阶数据库系列（九）：PostgreSQL 执行计划
民工哥的博客
07-07 1209
点击下方名片，设为星标！回复“1024”获取2TB学习资源！前面介绍了 PostgreSQL 常用管理命令、访问控制与认证、语法、数据类型、常用函数、锁操作相关的知识点，今天我将详细的为大家介绍 PostgreSQL 执行计划介绍相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！了解 PostgreSQL 执行计划对于程序员来说是一项关键技能，执行计划是我们优化查询，验证我...
【转载】PostgreSQL执行计划
荷塘月色
05-22 5708
简介 PostgreSQL是“世界上最先进的开源关系型数据库”。因为出现较晚，所以客户人群基数较MySQL少，但是发展势头很猛，最大优势是完全开源。 MySQL是“世界上最流行的开源关系型数据库”。当前客户基数大，随着被Oracle收购，开源程度减小，尤其是近期单独拉了免费的MariaDB分支，更表明MySQL有闭源的倾向； 至于两者孰优孰劣，不是本文要讨论的重点，在一般的使用中，没什么大的差别，下面我们只讨论PG中执行计划。 执行计划 pg在查询规划路径过程中，查询请求的不同执行方案是通过建立不同的路径来
【数据库】 - postgresql数据库执行计划
wangdong的博客
07-31 2287
数据库 - 执行计划 1. 执行计划命令 explain 2. 命令参数 analyze：选项为TRUE 会实际执行SQL，并获得相应的查询计划，默认为FALSE buffers：buffers必须跟analyze一起使用，只有真实执行SQL才能获取缓冲区信息 costs：表示每一个计划节点的估计启动和总代价，以及估计的行数和每行的宽度 Verbose ：显示关于计划的额外信息 ，例如：节点输出的列（Output） Format（text，xml，json，yaml）（格式化输出）
Postgresql | 执行计划
Albirostris
03-09 2813
postgresql 执行计划
PostGreSQL.txt
06-07
pg数据库经常使用的一些命令总结，方便初学者进行学习，非常实用。
快速上手python操作postgresql
01-20
sql上传copy_from上传python读取数据库数据为dataframe创建connection对象pd.read_sql读取数据库python操作数据库执行sql语句创建connection对象创建cursor对象cursor对象执行sql语句学习链接 python上传dataframe...
postgres_opttune:使用机器学习尝试PostgreSQL参数调整
05-04
使用pgbench进行调整时，请执行以下步骤。 python3安装 # yum install python3 python3-devel python3-libs python3-pip # pip3 install --upgrade pip setuptools 编译器（gcc）安装 # yum install gcc git安装 #...
learning-nodejs:学习目标
05-12
了解Node.js和GraphQL 路线图 准备 学习Node.js ...执行 路由 验证 分页 快取 目标 使用Node.js和GraphQL创建API 使用Vue.js和Apollo在前端使用GraphQL 使用Node.js构建GraphQL Server 立即或立即部署
【PostgreSQL】PostgreSQL的EXPLAIN命令详解
最新发布
tttzzzqqq2018的博客
12-28 2281
【详解】【PostgreSQL】PostgreSQL的EXPLAIN命令详解。
PostgreSQL--读懂执行计划(一)
qq_37531186的博客
10-10 7814
这里写自定义目录标题前言执行计划常用命令参数解读常用组合执行计划解读关键字常见扫描方式Seq ScanIndex Only ScanIndex ScanBitmap Index Scan+Bitmap Heap ScanHash JoinNested LoopMerge Join小结 前言 PostgreSQL为每个收到查询产生一个查询计划。 选择正确的计划来匹配查询结构和数据的属性对于好的性能来说绝对是最关键的，因此系统包含了一个复杂的规划器来尝试选择好的计划。 你可以使用EXPLAIN命令察看规划器为任
Postgresql学习
热门推荐
m0_46486963的博客
06-25 1万+
PostgreSQL中的序列是一个数据库对象，本质上是一个自增器。所以，Sequence也可以通过在每个属性后加上autoincrment的值的形式存在。1.作为表的唯一标识符字段的默认值使用2.主要用于jilushujukuzhongdeid，只要语句有动作（I|U|D),sequence的号就会随着更新。Sequence对象中包含当前值，和一些独特属性，例如如果递增（或者递减），实际上Sequence是不能被直接访问到的；他们需要通过PostgreSQL中的相关函数来操作。
mysql 物化视图_postgreSQL中的视图和物化视图
weixin_36102930的博客
01-27 273
postgreSQL中的视图和物化视图视图和物化视图区别postgres中的视图和mysql中的视图是一样的，在查询的时候进行扫描子表的操作，而物化视图则是实实在在地将数据存成一张表。说说版本，物化视图是在9.3 之后才有的逻辑。比较下视图和物化视图的性能创建两个表CREATE TABLE teacher (id int NOT NULL,sname varchar(100));CREATE TA...
postgresql -- 执行计划
Hehuyi_In的博客
11-10 4387
一、 显示执行计划 pg中explain命令格式如下 explain [options] sql语句 例如 explain select * from test; explain (format xml) select * from test; explain (analyze true,buffers true) select * from test; options可选项如下： A...
PostgreSql 执行计划
脑子进水养啥鱼？的博客
02-09 3763
语法 explain (analyze true|false,verbose true|false,costs true|false,buffers true|false,format text|xml|json|yaml) analyze：真实执行sql获取执行计划，dml语句不想改变数据库数据可放入事务，执行完后回滚，该选项默认值为false。 verbose：用于显示计划的附加信息，附加信息有计划熟中每个节点输出的各个列，如果触发器被触发，还会输出触发器的名称，该选项默认值为false。 cost
postgres dead tuple引起的执行计划错误
乡下人
08-21 195
amrdb=> \d+ t_index Table "XXXXXX_shm.t_index" Column | Type | Modifiers | Storage | Stats target | Description --------+-----------------------------+-----------+----------+-----.
PostgreSQL执行计划获取与修改
君子不怨天的博客
09-26 1008
auto_explain插件，能够把执行时间较长的SQL及执行计划写到PG日志里。禁用enable_indexscan后，查询走bitmap heap scan。查看执行计划的网站：https://explain.depesz.com/禁用enable_bitmapscan后，查询再次走全表扫描。通过用时，和统计信息两种方式，都能看出二者差别。注意：explain后跟多个选项时，需要加括号。指定索引列条件，执行计划走index scan。pg中通过修改执行计划选项开关来修改执行计划。
postgresql学习
08-24
要学习PostgreSQL，你可以按照以下步骤进行操作。首先，创建一个包含用户定义函数和类型的C文件，并编译它。然后，通过执行以下命令来开始教程：``` psql -s mydb mydb=> \i /home/ubuntu/postgresql-12.15/src/tutorial/basics.sql ``` 另外，在Linux系统中，你可以使用以下步骤来启动和关闭数据库服务。首先，使用以下命令查看服务进程： ``` ps aux | grep postgres ``` 然后，切换到PostgreSQL安装目录下的postgres用户： ``` cd /PostgreSQL/9.3/bin su postgres ``` 接下来，使用pg_ctl命令关闭服务。你可以使用以下命令来关闭服务： ``` ./pg_ctl stop -D /PostgreSQL/9.3/data ``` 其中，-D指向数据文件所存储的目录。你还可以使用以下命令来关闭服务并将日志保存在指定目录中： ``` ./pg_ctl stop -D /PostgreSQL/9.3/data -l /PostgreSQL/9.3/data/s ``` 其中，-l指向服务启动时日志所存储的目录。 另外，如果你遇到了"createdb: could not connect to database postgres: FATAL: role "joe" does not exist"的错误提示，这可能是因为"joe"用户角色不存在。你可以尝试创建一个名为"joe"的角色，并确保正确连接到数据库后再执行相应的操作。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *3* [PostgreSQL 基础（一）-- 新手教程](https://blog.csdn.net/chinusyan/article/details/130532405)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* [PostgreSQL学习](https://blog.csdn.net/Achard_Wang/article/details/118143175)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值