Postgresql 查看SQL语句执行效率的操作

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看 SQL 语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。

Explain语法：

explain select … from … [where ...]

例如：

explain select * from dual;

这里有一个简单的例子，如下：

EXPLAIN SELECT * FROM tenk1;
               QUERY PLAN
----------------------------------------------------------------
   Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)

EXPLAIN引用的数据是：

1). 预计的启动开销(在输出扫描开始之前消耗的时间，比如在一个排序节点里做排续的时间)。

2). 预计的总开销。

3). 预计的该规划节点输出的行数。

4). 预计的该规划节点的行平均宽度(单位：字节)。

这里开销(cost)的计算单位是磁盘页面的存取数量，如1.0将表示一次顺序的磁盘页面读取。其中上层节点的开销将包括其所有子节点的开销。这里的输出行数(rows)并不是规划节点处理/扫描的行数，通常会更少一些。一般而言，顶层的行预计数量会更接近于查询实际返回的行数。

现在我们执行下面基于系统表的查询：

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

从查询结果中可以看出tenk1表占有358个磁盘页面和10000条记录，然而为了计算cost的值，我们仍然需要知道另外一个系统参数值。

postgres=# show cpu_tuple_cost;
   cpu_tuple_cost
  ----------------
   0.01
  (1 row)
cost = 458(磁盘页面数) + 10000(行数) * 0.01(cpu_tuple_cost系统参数值

补充：postgresql SQL COUNT(DISTNCT FIELD) 优化

背景

统计某时段关键词的所有总数，也包含null （statistics 有400w+的数据，表大小为 600M），故

写出sql：

select count(distinct keyword) +1 as count from statistics;

问题

虽然是后台查询，但是太慢了，执行时间为为 38.6s，那怎么优化呢？

解决

方法1（治标）

把这个定时执行，然后把sql结果缓存下，然后程序访问缓存结果，页面访问是快了些，但是本质上还没有解决sql执行慢的问题。

方法2（治本）

优化sql，首先说说 count( distinct FIELD) 为啥这么慢，此处不再赘述了，请看这篇：https://www.jb51.net/article/65680.htm

优化内容：

select count( distinct FIELD ) from table

修改为

select count(1) from (select distinct FIELD from table) as foo;

比较

执行过程比对，可以使用 explian anaylze sql语句 查看

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • PostgreSQL中的VACUUM命令用法说明

    每当PostgreSQL数据库中的表中的行被更新或删除时,死亡行会被遗留下来.VACUUM则会把它们除去来使空间能被重新利用.如果一个表没有被清空,它会变得臃肿,浪费磁盘空间而且会降低顺序表扫描的速度,而且在较小范围内也会降低索引扫描的速度. VACUUM命令只可以移除这些不再被需要的行版本(也被称为元组).如果被删除事务的事务ID(存储在xmax系统列中)比仍然活跃在PostgreSQL数据库(或者共享表的整个集群)中最老的事务(xmin界限)更老,那么这个元组将不再被需要. 注意以下三种情况

  • postgreSQL使用pgAdmin备份服务器数据的方法

    postgreSQL使用pgAdmin备份服务器数据的方法

    使用postgresql的时候,有时候需要备份数据,但是数据库又比较多,比如我们的数据库如下: 这就很烦了,需要一种一键备份还原的方法来备份还原. 1.备份 备份可以使用pgadmin自带的备份服务器.右键服务器连接,点击备份服务器,再选择创建的文件与数据库编码(一般为UTF8),即可,感觉比命令行的方式好用多了. 当然也可以使用命令行的方式,以下来自百度,试过没有成功,觉得太麻烦了就放弃了,供参考: SQL备份 pg_dumpall.exe -h localhost -p 5432 -U po

  • PostgreSQL备份工具 pgBackRest使用详解

    前言 pgBackRest是一款开源的备份还原工具,目标旨在为备份和还原提供可靠易用的备份. 特性 并行备份和还原 备份操作期间压缩通常是其瓶颈所在.pgBackRest通过并行处理解决了备份期间压缩出现的瓶颈问题. 本地远程操作 自定义协议允许 pgBackRest以最小化配置通过SSH在本地或者远程执行备份.还原和归档.并且该程序也通过协议层提供了PostgreSQL查询接口,以便于必须要再远程访问PostgreSQL,从而保证了其安全性能. 全量,增量和差异备份 支持全量,增量和差异备份.

  • PostgreSQL 如何查找需要收集的vacuum 表信息

    前言 通常,在PostgreSQL中,由于经常需要对表进行 UPDATE 和 DELETE,因此表会产生碎片空间. 在 PostgreSQL中,使用VACUUM 仅仅对需要执行 VACUUM 表将已删除的空间标识为未使用,以便以后重用这些空间,但是不能立即将占用的空间返还给操作系统,因此需要使用 VACUUM FULL,才可以释放空间,并立即将空间返还给操作系统. 实现脚本 记录收集表创建 CREATE TABLE IF NOT EXISTS tab_vacuum_record (sqltext

  • PostgreSQL对GROUP BY子句使用常量的特殊限制详解

    一.问题描述 最近,一个统计程序从Oracle移植到PostgreSQL(版本9.4)时,接连报告错误: 错误信息1: postgresql group by position 0 is not in select list. 错误信息2: non-integer constant in GROUP BY. 产生错误的sql类似于: insert into sum_tab (IntField1, IntField2, StrField1, StrField2, cnt) select IntFi

  • postgresql数据合并,多条数据合并成1条的操作

    postgresql数据合并,多条数据合并成1条的操作

    对于主表中一条记录,对应明细表中的96条数据,每一条数据相隔15分钟,明细中没96条数据对应主表中的一个日期trade_date,并且每条明细中有一个字段start_time, 即明细中每96条数据中第一条数据中start_time为00:00, 第二条为00:15,第三条为00:30,依次类推,直到23:45 ,现在要将明细表中的96条数据合并成24条,即第一条数据中start_time为00:00,第二条为01:00,第三条为02:00 sql:select max(de.bid_num)

  • postgresql数据添加两个字段联合唯一的操作

    postgresql数据添加两个字段联合唯一的操作

    我就废话不多说了,大家还是直接看代码吧~ alter table tb_safety_commitment add constraint uk_mac_vendor unique (company_code,promise_date); 补充:一行数据中的 多字段值根据连接符拼接 concat_ws(':',a,b) 几行数据中的 同一 单字段值根据连接符拼接 string_agg(c,' \r\n ') 如果要将多个字段的值拼接成一个: string_agg(concat_ws(':',a,b

  • Postgresql 查看SQL语句执行效率的操作

    Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看 SQL 语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句. Explain语法: explain select - from - [where ...] 例如: explain select * from dual; 这里有一个简单的例子,如下: EXPLAIN SELECT * FROM tenk1; QUERY PLAN ---------

  • 根据mysql慢日志监控SQL语句执行效率

    根据mysql慢日志监控SQL语句执行效率 启用MySQL的log-slow-queries(慢查询记录). 在Linux环境下先要找到my.cnf文件(一般在/etc/mysql/),然后可能会发现该文件修改后无法保存,原因是你没有相应的权限,可以从属性中看到该文件的所有者是root,这时要先以root的身份打开它: sudo nautilus /etc/mysql 接着再打开my.cnf文件然后找到[mysqld]标签在下面加上: log-slow-queries=/path/slow.lo

  • 一条 SQL 语句执行过程

    一条 SQL 语句执行过程

    目录 一.MySQL体系架构 -连接池组件 -缓存组件 -分析器 -优化器 -执行器 二.写操作执行过程 三.读操作执行过程 四.SQL执行顺序 一.MySQL 体系架构 - 连接池组件 1.负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据,而不能同时进行. 2.验证用户名和密码是否正确(数据库 MySQL 的 user 表中进行验证),如果错误返回错误通知Access denied for user 'root'@'localhost'

  • SQL Server并行操作优化避免并行操作被抑制而影响SQL的执行效率

    SQL Server并行操作优化避免并行操作被抑制而影响SQL的执行效率

    为什么我也要说SQL Server的并行: 这几天园子里写关于SQL Server并行的文章很多,不管怎么样,都让人对并行操作有了更深刻的认识. 我想说的是:尽管并行操作可能(并不是一定)存在这样或者那样的问题,但是我们不能否认并行,仍然要利用好并行. 但是,实际开发中,某些SQL语句的写法会导致用不到并行,从而影响到SQL的执行效率 所以,本文要表达的是:我们要利用好并行,不要让一些SQL的写法问题"抑制"了并行,让我们享受不了并行带来的快感 关于SQL Server的并行: 所谓的

  • 腾讯面试：一条SQL语句执行得很慢的原因有哪些？---不看后悔系列(推荐)

    说实话,这个问题可以涉及到 MySQL 的很多核心知识,可以扯出一大堆,就像要考你计算机网络的知识时,问你"输入URL回车之后,究竟发生了什么"一样,看看你能说出多少了. 之前腾讯面试的实话,也问到这个问题了,不过答的很不好,之前没去想过相关原因,导致一时之间扯不出来.所以今天,我带大家来详细扯一下有哪些原因,相信你看完之后一定会有所收获,不然你打我. 开始装逼:分类讨论 一条 SQL 语句执行的很慢,那是每次执行都很慢呢?还是大多数情况下是正常的,偶尔出现很慢呢?所以我觉得,我们还得

  • MySQL数据库基本SQL语句教程之高级操作

    MySQL数据库基本SQL语句教程之高级操作

    目录 前言: 一.克隆表 1.1克隆方法一(将表与内容分开克隆) 1.2克隆方法二(将表与内容一起复制) 二.清空表,删除表内的所有数据 2.1方法一 2.2方法二 2.3小小结之drop,truncate,eleted的对比 三.创建临时表 四.用户管理 4.1新建用户 4.2使用明文密码创建用户 4.3使用密文创建数据库 五.查看用户信息 六.重命名用户 七.删除用户 八.密码管理 8.1修改当前用户密码 8.2修改其他用户的密码 8.3忘记root密码 8.3.1给root设置密码 九.数

  • MySql中sql语句执行过程详细讲解

    目录 前言: sql语句的执行过程: 查询缓存: 分析器: 优化器: 执行器: 总结 前言: 很多人都在使用mysql数据库,但是很少有人能够说出来整个sql语句的执行过程是怎样的,如果不了解执行过程的话,就很难进行sql语句的优化处理,也很难设计出来优良的数据库表结构.这篇文章主要是讲解一下sql语句的执行过程. sql语句的执行过程: 客户端.连接器.分析器.优化器.执行器.存储引擎几个阶段. 连接器的作用:管理链接.权限验证的处理. 分析器的作用:词法分析.语法分析. 优化器的作用:执行计

  • SQL语句执行超时引发网站首页访问故障问题

    非常抱歉,今天早上 6:37~8:15 期间,由于获取网站首页博文列表的 SQL 语句出现突发的查询超时问题,造成访问网站首页时出现 500 错误,由此给您带来麻烦,请您谅解. 故障的情况是这样的. 故障期间日志中记录了大量下面的错误. 2020-02-03 06:37:24.635 [Error] An unhandled exception has occurred while executing the request./Microsoft.AspNetCore.Diagnostics.E

  • postgresql 常用SQL语句小结

    目录 1.查询链接数 2.查看死锁状态 3.删除死锁进程 4.备份数据库 5.还原数据库 6.插入数据 插入单条数据 插入多条数据 从一张表查询到的数据插入到另一张表 7 .查询pg中单张表的大小(不包含索引) 8.查询数据库中所有表的大小 9.按顺序查看索引 10 .查询数据库的大小 11.查询被锁定的表 12 .查询一个Schema下面的所有表的总大小(单位MB,包括索引和数据) 14 .查询表大小按大小排序并分离data与index 1.查询链接数 SELECT sum(numbacken

  • 在SQLServer上查看SQL语句的执行时间的方法

    1:下面这种是比较简单的查询方法,通过查询前的时间和查询后的时间差来计算的 复制代码 代码如下: declare @begin_date datetime declare @end_date datetime select @begin_date = getdate() <这里写上你的语句...> select @end_date = getdate() select datediff(ms,@begin_date,@end_date) as '用时/毫秒' 2:下面这种方法比较全面,将执行每

随机推荐