安装 PostgreSQL
PostgreSQL 是一个功能强大的开源对象关系数据库系统。它经过数十年的积极开发和经过验证的架构，使其在可靠性、数据完整性和正确性方面赢得了良好的声誉。而其天生支持异步操作的特性，在高并发场景中倍受欢迎。同时，它的扩展性极强，只要你有能力，你可以使用任何你熟悉的其它开发语言来编写 PostgreSQL 脚本，比如：Rust、Python 等。PostgreSQL 对象
本章将介绍 PostgreSQL 常见的服务器和数据库对象。示例数据库
本章将介绍示例数据库，以便后续的演示。同时，你将学习到如何将已存在的数据导入到 PostgreSQL 中。基础 SELECT
本章将讨论如何使用简单的 `SELECT` 语句从 PostgreSQL 查询数据。PostgreSQL 的 `SELECT` 语句有诸多独特的特性，让我们一起感受一下。WHERE 子句
本章我们将学习 `WHERE` 子句，它不但能用于 `SELECT` 过滤查询结果，还能用于其它语句。LIMIT、OFFSET 和 FETCH 子句
本章我们学习 `LIMIT` 、`OFFSET` 和 `FETCH` 子句。和 `LIMIT` 一样，`FETCH` 也是为了限定返回的行数，但你不知道的是，`FETCH` 才是 SQL 标准，而 `LIMIT` 不是。LIKE 和 ILIKE
前面章节提过，在 PostgreSQL 中，`LIKE` 是区分大小写的。如果要像其它数据库那样不区分大小写，需要使用 `ILIKE`。PostgreSQL 还为它们提供了等价的运算符。连接
本章将讨论 PostgreSQL 的各种连接：内连接、左外连接、右外连接、交叉连接、自然连接、自连接和完全外连接。值得一提的是，哪怕到了 MySQL 8，MySQL 依然不支持完全外连接。分组
本章将讨论 PostgreSQL 的 `GROUP BY` 子句：将`SELECT` 语句返回的结果进行分组；对于每个分组，可以使用聚合函数。同时讨论与之相关的 `HAVING`、`CUBE`、`ROLLUP` 等。联合查询（并集）、交集查询及差集查询
本章将讨论 `UNION`：联合（并集）查询、`INTERSECT `：交集查询和`EXCEPT`：差集查询。子查询
本章我们将讨论如何使用 PostgreSQL 子查询来构建复杂的查询。同时会学习 `IN`、`EXISTS`、`ANY`、`SOME`、`ALL` 等操作。插入数据
本章将讨论如何使用 `INSERT` 向 PostgreSQL 中插入新行。同时介绍如何通过`REGURNING`子句返回最新插入行的ID以及批量插入数据的方法。修改数据
本章将讨论如何使用 `UPDATE` 语句修改 PostgreSQL 数据，以及配合 `RETURNING` 子句在修改数据的同时，返回修改后的内容。最后，还将介绍 `UPDATE JOIN`：根据另一张表中的数据进行修改。删除数据
本章将讨论如何使用 `DELETE` 语句删除 PostgreSQL 数据，以及配合 `RETURNING` 子句在删除数据的同时，返回已删除的内容。最后，还将介绍 `DELETE JOIN`：根据另一张表中的数据进行删除。插入或更新数据
在关系型数据库中，术语`upsert`称为合并：当插入数据时，如果数据已存在则进行更新，否则插入新行。PostgreSQL 使用 `INSERT ON CONFLICT` 实现这一功能。基础数据类型
本章我们讨论 PostgreSQL 的基本数据类型：布尔型、字符型、数值型和日期时间型。这些类型与其它数据库有着很多不同，让我们一起深入细节进行了解。表
本章将讨论和表相关的知识，包括：创建表、修改表、清空（截断）表和删除表。约束
本章将讨论 PostgreSQL 的约束，包括：主键约束、外键约束、唯一约束、非空约束、 CHECK 约束。条件表达式和运算符
本章将讨论 PostgreSQL 条件表达式和运算符，包括：使用 `CASE` 构造条件查询、使用 `COALESCE` 过滤非空参数、使用 `NULLIF` 处理 `NULL` 值以及使用 `CAST` 进行数据类型转换。视图
本章我们将讨论视图：创建、修改、删除视图。为高级篇的物化视图、递归视图等打好基础。性能分析
PostgreSQL 提供了 `EXPLAIN` 语句，它可以用来分析 SQL 的执行情况。本章将对其进行讨论。索引
PostgreSQL 索引是增强数据库查询性能的有效工具。然而，索引增加了数据库系统的写入和存储开销。因此，正确使用它们非常重要。本章我们将讨论如何使用索引。角色与权限
本章介绍角色与权限。PostgreSQL 使用角色来表示用户账号，而不是其它数据库那样使用用户概念。客户端鉴权
本章将讨论 PostgreSQL 客户端鉴权。回到之前安装 PostgreSQL 时的一个问题，为什么在本地登录 PostgreSQL 时，不需要输入密码？本章将回答这个问题。事务
本章将讨论如何使用 `BEGIN` 、 `COMMIT` 和 `ROLLBACK` 语句处理 PostgreSQL 事务。备份与还原
本章将介绍备份和还原 PostgreSQL 数据库。常用函数
本章对 PostgreSQL 常用函数进行汇总，包括：聚合函数、日期时间函数、字符串函数和数学函数。对于窗口函数，我们将在高级篇进行介绍。
性能分析
- 437
- 2023-08-01 03:35:09
简介
explain 语句返回 PostgreSQL 规划器为给定 SQL 语句生成的执行计划。它显示了一条语句涉及的表将通过索引扫描或顺序扫描等方式进行扫描，以及如果使用多张表，将使用什么样的连接算法。
它的语法如下：
EXPLAIN [ ( 选项 [, ...] ) ] SQL语句;
选项 的可选值是：
以下选项的
boolen是一个开关，通过传递TRUE或FALSE来打开或关闭某个选项。
以下选项的 boolen 是一个开关，通过传递 TRUE 或 FALSE 来打开或关闭某个选项。
ANALYZE [ boolean ]：该选项导致先执行SQL语句，然后返回实际运行时的统计信息，包括每个计划节点花费的总运行时间以及实际返回的行数。它真实地执行了 SQL 语句，并丢弃执行结果。如果要分析INSERT/UPDATA/DELETE等语句，并且不想影响实际数据，应该将explain放在事务中，并进行回滚。VERBOSE [ boolean ]：显示有关计划的附加信息COSTS [ boolean ]：包括每个计划节点的估计成功和总成本。以及查询计划中的估计行数和每行的估计宽度。BUFFERS [ boolean ]：向缓冲区添加信息，仅在ANALYZE启用是才能使用。TIMING [ boolean ]：输出实际启动时间和每个节点花费的时间。仅在启用ANALYZE时才能使用。SUMMARY [ boolean ]：在查询计划后添加摘要信息，比如总计时。FORMAT { TEXT | XML | JSON | YAML }：指定查询计划的输出格式，默认为TEXT。
示例
对 film 进行简单的 EXPLAIN
EXPLAIN SELECT * FROM film;
/*结果
QUERY PLAN
----------------------------------------------------------
Seq Scan on film (cost=0.00..64.00 rows=1000 width=384)
*/
按指定 film_id 返回电影的 EXPLAIN
EXPLAIN SELECT * FROM film WHERE film_id = 100;
/*结果
QUERY PLAN
------------------------------------------------------------------------
Index Scan using film_pkey on film (cost=0.28..8.29 rows=1 width=384)
Index Cond: (film_id = 100)
*/
由于 film_id 已建立索引，所以它使用的是按索引扫描，而不是顺序扫描。
要抑制成本，可以使用 COSTS 选项：
EXPLAIN SELECT COUNT(*) FROM film;
/* 结果
QUERY PLAN
--------------------------------------------------------------
Aggregate (cost=66.50..66.51 rows=1 width=8)
-> Seq Scan on film (cost=0.00..64.00 rows=1000 width=0)
*/
使用多表连接：
EXPLAIN
SELECT
f.film_id,
title,
name category_name
FROM
film f
INNER JOIN film_category fc
ON fc.film_id = f.film_id
INNER JOIN category c
ON c.category_id = fc.category_id
ORDER BY
title;
/* 结果
QUERY PLAN
--------------------------------------------------------------------------------------
Sort (cost=149.64..152.14 rows=1000 width=87)
Sort Key: f.title
-> Hash Join (cost=77.86..99.81 rows=1000 width=87)
Hash Cond: (fc.category_id = c.category_id)
-> Hash Join (cost=76.50..95.14 rows=1000 width=21)
Hash Cond: (fc.film_id = f.film_id)
-> Seq Scan on film_category fc (cost=0.00..16.00 rows=1000 width=4)
-> Hash (cost=64.00..64.00 rows=1000 width=19)
-> Seq Scan on film f (cost=0.00..64.00 rows=1000 width=19)
-> Hash (cost=1.16..1.16 rows=16 width=72)
-> Seq Scan on category c (cost=0.00..1.16 rows=16 width=72)
*/
EXPLAIN ANALYZE
SELECT
f.film_id,
title,
name category_name
FROM
film f
INNER JOIN film_category fc
ON fc.film_id = f.film_id
INNER JOIN category c
ON c.category_id = fc.category_id
ORDER BY
title;
/* 结果 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Sort (cost=149.64..152.14 rows=1000 width=87) (actual time=3.652..3.693 rows=1000 loops=1)
Sort Key: f.title
Sort Method: quicksort Memory: 93kB
-> Hash Join (cost=77.86..99.81 rows=1000 width=87) (actual time=2.266..2.769 rows=1000 loops=1)
Hash Cond: (fc.category_id = c.category_id)
-> Hash Join (cost=76.50..95.14 rows=1000 width=21) (actual time=2.022..2.398 rows=1000 loops=1)
Hash Cond: (fc.film_id = f.film_id)
-> Seq Scan on film_category fc (cost=0.00..16.00 rows=1000 width=4) (actual time=0.100..0.151 rows=1000 loops=1)
-> Hash (cost=64.00..64.00 rows=1000 width=19) (actual time=1.894..1.895 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 59kB
-> Seq Scan on film f (cost=0.00..64.00 rows=1000 width=19) (actual time=0.013..1.743 rows=1000 loops=1)
-> Hash (cost=1.16..1.16 rows=16 width=72) (actual time=0.183..0.184 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on category c (cost=0.00..1.16 rows=16 width=72) (actual time=0.137..0.139 rows=16 loops=1)
Planning Time: 0.300 ms
Execution Time: 3.793 ms
*/