PostgreSQL学习系列—EXPLAIN ANALYZE查询计划解读

PostgreSQL命令 EXPLAIN ANALYZE 是日常工作中了解和优化SQL查询过程所用到的最强大工具，后接如 SELECT ... ， UPDATE ... 或者 DELETE ... 等SQL语句，命令执行后并不返回数据，而是输出查询计划，详细说明规划器通过何种方式来执行给定的SQL语句。

十余年的栾川网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。营销型网站建设的优势是能够根据用户设备显示端的尺寸不同，自动调整栾川建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联从事“栾川网站设计”,“栾川网站推广”以来，每个客户项目都认真落实执行。

下面是从 Postgres Using EXPLAIN 提取的查询：

它生成的查询计划：

Postgres构建了一个规划节点的树结构，以表示所采取的不同操作，其中root根和每个 - 指向其中一个操作。在某些情况下， EXPLAIN ANALYZE 会提供除执行时间和行数之外的额外执行统计信息，例如上面例子中的 Sort 及 Hash 。除第一个没有 - 的行之外的任何行都是诸如此类的信息，因此查询的结构是：

每个树分支代表子动作，从里到外以确定哪个是“第一个”发生（尽管同一级别的节点顺序可能不同）。

在 tenk_unique1 索引上执行的第一个操作是 Bitmap Index Scan ：

这对应于SQL WHERE t1.unique1 100 。Postgres查找与条件 unique1 100 匹配的行位置。此处不会返回行数据本身。成本估算 (cost=0.00..5.04 rows=101 width=0) 意味着Postgres预期将“花费” 任意计算单位的 5.04 来找到这些行。0.00是此节点开始工作的成本（在这种情况下，即为查询的启动时间）。 rows 是此索引扫描将返回的预估行数， width 是这些返回行的预估大小（以字节为单位）（0是因为这里只关心位置，而不是行数据的内容）。

因为使用了 ANALYZE 选项运行 EXPLAIN ，所以查询被实际执行并捕获了计时信息。 (actual time=0.049..0.049 rows=100 loops=1) 表示索引扫描执行了1次（ loops 值），结果返回了100行，实际时间是0 ..如果节点执行了多次，实际时间是每次迭代的平均值，可以将该值乘以循环次数以获取实际时间。基于成本的最小/最大时间的概念，范围值也可能会有所不同。通过这些值，我们可以为该查询生成一个成本比率，每个成本单位为0.049ms / 5.04单位≈0.01ms/单位。

索引扫描的结果将传递给 Bitmap Heap Scan 操作。在此节点中，Postgres将获取别名为t1的tenk1表中行的位置，根据 unique1 100 条件筛选并获取行。

当乘以之前计算的0.01值时，我们可以得到成本预期的大概时间（229.20 - 5.07）*0.01≈2.24ms，同时每行实际时间为除以4后的结果：0.526ms。这可能是因为成本估算是取的上限而不是取所有需读取的行，也或者因为Recheck条件总是生效。

和表顺序读取行（a Seq Scan ）相比， Bitmap Index Scan 和 Bitmap Heap Scan 关联操作成本要昂贵得多，但是因为在这种情况下只需要访问相对较少的行，所以关联操作最终会变得更快。通过在获取行之前将行按照物理顺序排序来进一步加速，这会将单独获取的成本降到最低。节点名称中的“Bitmap”完成了排序操作。

表扫描的结果（tenk1表中满足 unique1 100 条件的那些行）将在读取时被插入到内存的哈希表中。正如我们从成本中看到的那样，这根本不需要时间。

哈希节点包括散列桶(hash buckets)和批次数(batches)相关的信息，以及内存使用峰值情况。如果批次 1，则还会包括未显示的磁盘使用信息。内存占用在100行* 244字节= 24.4 kB时是有意义的，它非常接近28kB，我们假定这是哈希键本身所占用的内存。

接下来，Postgres从别名为t2的tenk2表读取所有的10000行，并根据tenk1表行的Hash检查它们。散列连接意味着将一个表的行输入到内存中的散列（先前的操作中已构建），之后扫描另一个表的行，并根据散列表探测其值以进行匹配。在第二行可以看到“匹配”的条件， Hash Cond: (t2.unique2 = t1.unique2) 。请注意，因为查询是从tenk1和tenk2中选择所有值，所以在散列连接期间每行的宽度加倍。

现在已经收集了满足条件的所有行，可以对结果集进行排序 Sort Key: t1.fivethous 。

Sort节点包含排序算法 quicksort 相关的信息 ，排序是在内存中还是在磁盘上完成（这将极大地影响速度），以及排序所需的内存/磁盘空间量。

熟悉如何解读查询计划会非常有助于优化查询。例如，Seq Scan节点通常表示添加索引的必要性，读取速度可能要快得多。

翻译并编辑，原文出处：

三分钟！彻底搞懂PostgreSQL 和 MySQL 区别之分

PostgreSQL 和 MySQL 是将数据组织成表的关系数据库。这些表可以根据每个表共有的数据链接或关联。关系数据库使您的企业能够更好地了解可用数据之间的关系，并帮助获得新的见解以做出更好的决策或发现新的机会。

PostgreSQL 和 MySQL 都依赖于 SQL（结构化查询语言），这是与管理系统交互的标准语言。SQL 允许使用具有简单结构的几行源代码连接表，大多数非技术员工可以快速学习。

使用 SQL，分析师不需要知道订单表在磁盘上的位置、如何执行查找以查找特定订单或如何连接订单表和客户表。数据库编译查询并计算出正确的数据点。

MySQL 和 PostgreSQL 都支持 JavaScript Object Notation (JSON) 存储和传输数据，尽管 PostgreSQL 也支持 JSONB，这是 JSON 的二进制版本，它消除了键的重复和无关的空格。

除了传统的支持机制外，这两个数据库都提供强大的社区支持。

PostgreSQL，也称为 Postgres，是一种开源关系数据库，因其可靠性、灵活性和对开放技术标准的支持而享有盛誉。PostgreSQL 支持非关系和关系数据类型。它被称为当今可用的最兼容、最稳定和最成熟的关系数据库之一，并且可以轻松处理复杂的查询。

PostgreSQL 的特性包括：

PostgreSQL 这是一个“一刀切”的解决方案，适用于许多寻求经济高效的方法来改进其数据库管理系统 (DBMS) 的企业。它具有足够的可扩展性和多功能性，可以通过强大的扩展生态系统快速支持各种专业用例，涵盖时间序列数据类型和地理空间分析等工作。作为开源数据库解决方案构建的 PostgreSQL 完全不受许可限制、供应商锁定的可能性或过度部署的风险。PostgreSQL 通过对象关系数据库管理系统 (ORDBMS) 进行管理。

PostgreSQL 负责管理业务活动的在线事务处理 (OLTP)协议的企业数据库管理员提供了理想的解决方案，包括电子商务、客户关系管理系统 (CRM) 和财务分类帐。它也是管理接收、创建和生成的数据分析的理想选择。

这些是 PostgreSQL 的一些主要优点：

MySQL — 一种快速、可靠、可扩展且易于使用的开源关系数据库系统 — 旨在处理关键任务、高负载的生产应用程序。它是一种常见且易于启动的数据库，内存、磁盘和 CPU 利用率较低，有关系数据库管理系统 (RDMS) 管理。MySQL Community Edition 是一个由活跃的在线社区支持的免费下载版本。

MySQL 功能包括所有 SQL 标准命令以及事务和 ACID 合规性（代表原子性、一致性、隔离性和持久性）。

两个最常见的关系数据库是什么 MySQL 和 Oracle。MySQL 不是 SQL Server 的同义词，SQL Server 是 Microsoft 许可产品，与 MAC OS X 缺乏兼容性。

MariaDB 经常与 MySQL 混淆，它是 MySQL 的一个开源分支，速度更快，提供更多存储引擎 (12)，但功能有限。MySQL 和 MariaDB 使用的存储引擎都是 InnoDB。InnoDB 提供标准的 ACID 兼容特性。与 MySQL 不同，MariaDB 不支持数据屏蔽或动态列表。

MySQL 通常用作 Web 数据库来存储各种信息类型，从单个信息数据点到为组织提供的产品或服务的完整列表。它是LAMP（Linux 操作系统、Apache HTTP 服务器、MySQL RDBMS 和 PHP 编程语言）的基础组件，这是一种有助于创建API、Web 应用程序和网站的软件堆栈模型。

MySQL Workbench 是一个单一的、集成的可视化 SQL 平台，用于 MySQL 数据库的创建、开发、设计和管理。

MySQL 为市场提供了许多好处，包括：

PostgreSQL 和 MySQL 之间有很多不同之处。特性、功能和优势方面的一些差异如下：

总之，PostgreSQL 和 MySQL 都有不同的用途，它们之间的选择取决于企业目标和资源。一般来说，PostgreSQL 是一个更强大、更高级的数据库管理系统，非常适合需要在大型环境中快速执行复杂查询的组织。但是，对于预算和空间更受限制的公司来说，MySQL 是一个理想的解决方案。

技术文档 - PostgreSQL 性能优化之 fsync 参数

目 录

总 结

PostgreSQL 通过调用系统 fsync() 或者其他使得事务内容写入到物理磁盘，这样可以保证操作系统或者数据库出现宕机后，仍然可以恢复到某一个一致性的状态。理论上讲 PostgreSQL 的 fsync 功能关闭，可以实现性能的提升，但是带来的影响就是需要承担数据的丢失，因为出现系统宕机或者数据库崩溃的时候有一些数据是没有落盘的。

本文将验证 fsync 参数的性能影响，以及参数关闭时数据库宕机后的影响。

数据量：1000W

fsync 参数：on

初始化表：user_info

pgbench 压测

pgbench 结果

pgbench 压测

pgbench 结果

数据量：1000W

fsync 参数：off

初始化表：user_info

pgbench 压测

pgbench 结果

pgbench 压测

pgbench 结果

通过对比发现，将 fsync 改为 off，对于读 TPS，参数 fsync 的影响不大，对于写 TPS，性能有一定提升。

现在验证参数关闭时数据库宕机后的影响

首先，使用将数据库性能跑起来

然后，模拟服务器断电

之后，启动数据库

提示信息：比致命错误还过分的错误。

结果：数据库无法启动，原因就是因为无法找到一个有效的 checkpoint 记录，这就是因为 fsync 设置为 off，由于数据库异常宕机导致。可以通过使用 pg_resetxlog 恢复数据库，但是会造成部分数据无法找回，数据丢失；也可以通过备份恢复，同样也会丢失部分数据。

fsync 参数对于读 TPS 的性能影响不大，对于写 TPS 的性能有一些影响，设置为 off，写 TPS 性能有一定提升，但是存在数据库宕机后无法正常启动，即使恢复后启动数据库，也会有数据丢失的很大风险。因此生产环境非必要时，不要将此参数设置为 off，还是使用默认的 on 比较稳妥。

标题名称：包含postgresql磁盘的词条
URL分享：http://scyingshan.cn/article/dsciejj.html