postgresql分页查询的简单介绍

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

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

下面是从 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节点通常表示添加索引的必要性，读取速度可能要快得多。

翻译并编辑，原文出处：

相关问答

Q1: PostGreSQL Json数据存储和条件查询

创建一张志愿者的数据表,记录每批参加志愿活动的人员名单。其中人员信息保存在json字段中。

知识点 : (1)postgresql中自增长的id创建。 (2)修改表字段语句。 (3)标准sql中table name ,column name双引号。

查询年龄大于等于25岁以上的志愿者

知识点 : (1)查询结果的的row number生成。 (2)获取json对象中的子对象。 (3)转换json对象属性的数据类型。

Q2: postgresql是什么数据库

postgresql是功能强大的开源数据库。

postgresql数据库是功能强大的开源数据库，它支持丰富的数据类型（如JSON和JSONB类型、数组类型）和自定义类型。

PostgreSQL数据库提供了丰富的接口，可以很方便地扩展它的功能，如可以在GiST框架下实现自己的索引类型，支持使用C语言写自定义函数、触发器，也支持使用流行的编程语言写自定义函数。

PostgreSQL数据库优点

1、对超许可数量软件使用的天然免疫力

对一些商业性质的数据库销售商来说，超许可数量的软件使用是第一位的问题。而使用PostgreSQL，因为没有任何授权的费用是，也就没有任何人可以起诉您违反授权协议违规使用软件。

2、比商业服务商更好的支持

除了有一般商业公司的支持外，我们还有由PostgreSQL专业人员和热心的爱好者组成的各种社区，用户可以向他们寻求支持和帮助。

3、员工成本的显著降低

相对于市场上大型的商业私有数据库软件，我们的系统在设计和开发时已考虑仅需少量的维护和优化，同时仍保持所有功能正常、稳定和性能正常。不仅如此，我们的培训过程相对于那些商业私有数据库供应商来说，总体来说有更好的性价比、更容易管理和更接近真实的使用场景。

4、享有盛名的可靠性和稳定性

与很多商业私有数据库不一样的是，对很多公司特别常见的是，PostgreSQL在几年的运行过程中几乎未出现哪怕是一次的宕机情况，即使是一次，它就是这样稳定地工作着。

5、扩展性

所有PostgreSQL的源代码对所有人都是可以免费获得的。如果您的公司员工需要定制或是给PostgreSQL增加一些功能，他们只需做很少的改动工作，也没有额外的成本。全球PostgreSQL社区的专业人员和热心的爱好者也在积极地扩展PostgreSQL的功能。

Q3: postgresql是什么数据库？请详解一下，谢谢

分类: 电脑/网络 程序设计 其他编程语言

解析:

PostgreSQL 是一种非常复杂的对象-关系型数据库管理系统（ORDBMS）， 也是目前功能最强大，特性最丰富和最复杂的自由软件数据库系统。有些特性甚至连商业数据库都不具备。这个起源于伯克利（BSD）的数据库研究计划目前已经衍生成一项国际开发项目， 并且有非常广泛的用户。

PostgreSQL 可以说是最富特色的自由数据库管理系统，甚至我们也可以说是最强大的自由软件数据库管理系统。事实上， PostgreSQL 的特性覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99，首先，它包括了可以说是目前世界上最丰富的数据类型的支持，其中有些数据类型可以说连商业数据库都不具备， 比如 IP 类型和几何类型等；其次，PostgreSQL 是全功能的自由软件数据库，很长时间以来，PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统、数据完整性检查等特性的唯一的一种自由软件的数据库管理系统。直到最近才有 Inprise 的 InterBase 以及 SAP 等厂商将其原先专有软件开放为自由软件之后才打破了这个唯一。

Q4: 如何提高postgresql查询性能

一、使用EXPLAIN：

PostgreSQL为每个查询都生成一个查询规划，因为选择正确的查询路径对性能的影响是极为关键的。PostgreSQL本身已经包含了一个规划器用于寻找最优规划，我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划。

PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树，其中最底层的节点为表扫描节点，用于从数据表中返回检索出的数据行。然而，不同

的扫描节点类型代表着不同的表访问模式，如：顺序扫描、索引扫描，以及位图索引扫描等。如果查询仍然需要连接、聚集、排序，或者是对原始行的其它操作，那

么就会在扫描节点"之上"有其它额外的节点。并且这些操作通常都有多种方法，因此在这些位置也有可能出现不同的节点类型。EXPLAIN将为规划树中的每

个节点都输出一行信息，显示基本的节点类型和规划器为执行这个规划节点计算出的预计开销值。第一行(最上层的节点)是对该规划的总执行开销的预计，这个数

值就是规划器试图最小化的数值。

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

复制代码 代码如下:

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 = 358(磁盘页面数) + 10000(行数) * 0.01(cpu_tuple_cost系统参数值)

下面我们再来看一个带有WHERE条件的查询规划。

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 7000;

QUERY PLAN

------------------------------------------------------------

Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)

Filter: (unique1 7000)

EXPLAIN的输出显示，WHERE子句被当作一个"filter"应用，这表示该规划节点将扫描表中的每一行数据，之后再判定它们是否符合过滤的条

件，最后仅输出通过过滤条件的行数。这里由于WHERE子句的存在，预计的输出行数减少了。即便如此，扫描仍将访问所有10000行数据，因此开销并没有

真正降低，实际上它还增加了一些因数据过滤而产生的额外CPU开销。

上面的数据只是一个预计数字，即使是在每次执行ANALYZE命令之后也会随之改变，因为ANALYZE生成的统计数据是通过从该表中随机抽取的样本计算的。

如果我们将上面查询的条件设置的更为严格一些的话，将会得到不同的查询规划，如：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 100;

QUERY PLAN

------------------------------------------------------------------------------

Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

这里，规划器决定使用两步规划，最内层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点再从表里读取这些行。单独地读取数据行比顺

序地读取它们的开销要高很多，但是因为并非访问该表的所有磁盘页面，因此该方法的开销仍然比一次顺序扫描的开销要少。这里使用两层规划的原因是因为上层规

划节点把通过索引检索出来的行的物理位置先进行排序，这样可以最小化单独读取磁盘页面的开销。节点名称里面提到的"位图(bitmap)"是进行排序的机

制。

现在我们还可以将WHERE的条件设置的更加严格，如：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 3;

QUERY PLAN

------------------------------------------------------------------------------

Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)

Index Cond: (unique1 3)

在该SQL中，表的数据行是以索引的顺序来读取的，这样就会令读取它们的开销变得更大，然而事实上这里将要获取的行数却少得可怜，因此没有必要在基于行的物理位置进行排序了。

现在我们需要向WHERE子句增加另外一个条件，如：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 3 AND stringu1 = 'xxx';

QUERY PLAN

------------------------------------------------------------------------------

Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)

Index Cond: (unique1 3)

Filter: (stringu1 = 'xxx'::name)

新增的过滤条件stringu1 = 'xxx'只是减少了预计输出的行数，但是并没有减少实际开销，因为我们仍然需要访问相同数量的数据行。而该条件并没有作为一个索引条件，而是被当成对索引结果的过滤条件来看待。

如果WHERE条件里有多个字段存在索引，那么规划器可能会使用索引的AND或OR的组合，如：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 100 AND unique2 9000;

QUERY PLAN

-------------------------------------------------------------------------------------

Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)

Recheck Cond: ((unique1 100) AND (unique2 9000))

- BitmapAnd (cost=11.27..11.27 rows=11 width=0)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)

Index Cond: (unique2 9000)

这样的结果将会导致访问两个索引，与只使用一个索引，而把另外一个条件只当作过滤器相比，这个方法未必是更优。

现在让我们来看一下基于索引字段进行表连接的查询规划，如：

复制代码 代码如下:

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;

QUERY PLAN

--------------------------------------------------------------------------------------

Nested Loop (cost=2.37..553.11 rows=106 width=488)

- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

- Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)

Index Cond: ("outer".unique2 = t2.unique2)

从查询规划中可以看出(Nested

Loop)该查询语句使用了嵌套循环。外层的扫描是一个位图索引，因此其开销与行计数和之前查询的开销是相同的，这是因为条件unique1

100发挥了作用。 这个时候t1.unique2 =

t2.unique2条件子句还没有产生什么作用，因此它不会影响外层扫描的行计数。然而对于内层扫描而言，当前外层扫描的数据行将被插入到内层索引扫描

中，并生成类似的条件t2.unique2 = constant。所以，内层扫描将得到和EXPLAIN SELECT * FROM tenk2

WHERE unique2 = 42一样的计划和开销。最后，以外层扫描的开销为基础设置循环节点的开销，再加上每个外层行的一个迭代(这里是 106

* 3.01)，以及连接处理需要的一点点CPU时间。

如果不想使用嵌套循环的方式来规划上面的查询，那么我们可以通过执行以下系统设置，以关闭嵌套循环，如：

复制代码 代码如下:

SET enable_nestloop = off;

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;

QUERY PLAN

------------------------------------------------------------------------------------------

Hash Join (cost=232.61..741.67 rows=106 width=488)

Hash Cond: ("outer".unique2 = "inner".unique2)

- Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)

- Hash (cost=232.35..232.35 rows=106 width=244)

- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)

Index Cond: (unique1 100)

这个规划仍然试图用同样的索引扫描从tenk1里面取出符合要求的100行，并把它们存储在内存中的散列(哈希)表里，然后对tenk2做一次全表顺序扫

描，并为每一条tenk2中的记录查询散列(哈希)表，寻找可能匹配t1.unique2 =

t2.unique2的行。读取tenk1和建立散列表是此散列联接的全部启动开销，因为我们在开始读取tenk2之前不可能获得任何输出行。

此外，我们还可以用EXPLAIN ANALYZE命令检查规划器预估值的准确性。这个命令将先执行该查询，然后显示每个规划节点内实际运行时间，以及单纯EXPLAIN命令显示的预计开销，如：

复制代码 代码如下:

EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 100 AND t1.unique2 = t2.unique2;

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------

Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)

- Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)

Recheck Cond: (unique1 100)

- Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37

rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)

Index Cond: (unique1 100)

- Index Scan using tenk2_unique2 on tenk2 t2

(cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1

loops=100)

Index Cond: ("outer".unique2 = t2.unique2)

Total runtime: 14.452 ms

注意"actual time"数值是以真实时间的毫秒来计算的，而"cost"预估值是以磁盘页面读取数量来计算的，所以它们很可能是不一致的。然而我们需要关注的只是两组数据的比值是否一致。

在一些查询规划里，一个子规划节点很可能会运行多次，如之前的嵌套循环规划，内层的索引扫描会为每个外层行执行一次。在这种情况下，"loops"将报告

该节点执行的总次数，而显示的实际时间和行数目则是每次执行的平均值。这么做的原因是令这些真实数值与开销预计显示的数值更具可比性。如果想获得该节点所

花费的时间总数，计算方式是用该值乘以"loops"值。

EXPLAIN ANALYZE显示的"Total runtime"包括执行器启动和关闭的时间，以及结果行处理的时间，但是它并不包括分析、重写或者规划的时间。

如果EXPLAIN命令仅能用于测试环境，而不能用于真实环境，那它就什么用都没有。比如，在一个数据较少的表上执行EXPLAIN，它不能适用于数量很

多的大表，因为规划器的开销计算不是线性的，因此它很可能对大些或者小些的表选择不同的规划。一个极端的例子是一个只占据一个磁盘页面的表，在这样的表

上，不管它有没有索引可以使用，你几乎都总是得到顺序扫描规划。规划器知道不管在任何情况下它都要进行一个磁盘页面的读取，所以再增加几个磁盘页面读取用

以查找索引是毫无意义的。

二、批量数据插入：

有以下几种方法用于优化数据的批量插入。

1. 关闭自动提交：

在批量插入数据时，如果每条数据都被自动提交，当中途出现系统故障时，不仅不能保障本次批量插入的数据一致性，而且由于有多次提交操作的发生，整个插入效

率也会受到很大的打击。解决方法是，关闭系统的自动提交，并且在插入开始之前，显示的执行begin

transaction命令，在全部插入操作完成之后再执行commit命令提交所有的插入操作。

2. 使用COPY：

使用COPY在一条命令里装载所有记录，而不是一系列的INSERT命令。COPY命令是为装载数量巨大的数据行优化过的，它不像INSERT命令那样灵

活，但是在装载大量数据时，系统开销也要少很多。因为COPY是单条命令，因此在填充表的时就没有必要关闭自动提交了。

3. 删除索引：

如果你正在装载一个新创建的表，最快的方法是创建表，用COPY批量装载，然后创建表需要的任何索引。因为在已存在数据的表上创建索引比维护逐行增加要快。当然在缺少索引期间，其它有关该表的查询操作的性能将会受到一定的影响，唯一性约束也有可能遭到破坏。

4. 删除外键约束：

和索引一样，"批量地"检查外键约束比一行行检查更加高效。因此，我们可以先删除外键约束，装载数据，然后在重建约束。

5. 增大maintenance_work_mem：

在装载大量数据时，临时增大maintenance_work_mem系统变量的值可以改进性能。这个系统参数可以提高CREATE

INDEX命令和ALTER TABLE ADD FOREIGN KEY命令的执行效率，但是它不会对COPY操作本身产生多大的影响。

6. 增大checkpoint_segments：

临时增大checkpoint_segments系统变量的值也可以提高大量数据装载的效率。这是因为在向PostgreSQL装载大量数据时，将会导致

检查点操作(由系统变量checkpoint_timeout声明)比平时更加频繁的发生。在每次检查点发生时，所有的脏数据都必须flush到磁盘上。

通过提高checkpoint_segments变量的值，可以有效的减少检查点的数目。

7. 事后运行ANALYZE：

在增加或者更新了大量数据之后，应该立即运行ANALYZE命令，这样可以保证规划器得到基于该表的最新数据统计。换句话说，如果没有统计数据或者统计数据太过陈旧，那么规划器很可能会选择一个较差的查询规划，从而导致查询效率过于低下。

Q5: postgresql数据库怎么查询所有的序列名

postgresql数据库怎么查询所有的序列名

postgresql中一个序列对象通常用于为行或者表生成唯一的标识符。

查看序列：

psql 的 \d 命令输出一个数据库对象，包括 Sequence，表，视图和索引。你还可以使用 \ds 命令只查看当前数据库的所有序列。例如：

pigdb-# \ds

List of relations

Schema | Name | Type | Owner

--------+-----------------------+----------+--------

public | author_ids | sequence | ichexw

public | shipments_ship_id_seq | sequence | ichexw

(2 rows)

关于postgresql分页查询和的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。