PG系列5-SQL高级特性1（with查询，批量插入，数据抽样...）

最新推荐文章于 2024-05-14 18:08:10 发布

玻璃酸钠滴眼液

最新推荐文章于 2024-05-14 18:08:10 发布

阅读量1.2k

点赞数

分类专栏： PostgreSQL 文章标签：数据库 postgresql

本文链接：https://blog.csdn.net/weixin_41191813/article/details/118523192

版权

PostgreSQL 专栏收录该内容

8 篇文章 4 订阅

订阅专栏

文章目录

1、WITH查询
- 1.1 复杂查询使用CTE
- 1.2 递归查询使用CTE
2、批量插入
3、RETURNING返回修改的数据
4、UPSERT
- 4.1 UPSERT场景演示
- 4.2 UPSERT语法
5、数据抽样

1、WITH查询

WITH查询是PostgreSQL支持的高级SQL特性之一，这一特性常称为CTE（Common Table Expressions），WITH查询在复杂查询中定义一个辅助语句（可理解成在一个查询中定义的临时表），这一特性常用于复杂查询或递归查询应用场景。

1.1 复杂查询使用CTE

先通过一个简单的CTE示例了解WITH查询，如下所示：

WITH t as ( 
	SELECT generate_series(1,3) 
)
SELECT * FROM t;

执行结果如下：

generate_series 
----------------- 
	1
	2
	3 
(3 rows)

这个简单的CTE示例中，一开始定义了一条辅助语句t取数，之后在主查询语句中查询t，定义的辅助语句就像是定义了一张临时表，对于复杂查询如果不使用CTE，可以通过创建视图方式简化SQL。
CTE可以简化SQL并且减少嵌套，因为可以预先定义辅助语句，之后在主查询中多次调用。接着看一个稍复杂CTE例子，这个例子来自手册，如下所示：

WITH regional_sales AS ( 
		SELECT region, SUM(amount) AS total_sales 
		FROM orders 
		GROUP BY region 
		), 
		top_regions AS ( 
		SELECT region 
		FROM regional_sales 
		WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) 
SELECT region, 
		product, 
		SUM(quantity) AS product_units, 
		SUM(amount) AS product_sales 
FROM orders 
WHERE region IN (SELECT region FROM top_regions) 
GROUP BY region, product;

这个例子首先定义了regional_sales和top_regions两个辅助语句，regional_sales算出每个区域的总销售量，top_regions算出销售量占总销售量10%以上的所有区域，主查询语句通过辅助语句与orders表关联，算出了顶级区域每件商品的销售量和销售额。

1.2 递归查询使用CTE

WITH查询的一个重要属性是RECURSIVE，使用 RECURSIVE属性可以引用自己的输出，从而实现递归，一般用于层次结构或树状结构的应用场景，一个简单的 RECURSIVE例子如下所示：

WITH recursive t (x) as ( 
	SELECT 1 
	UNION SELECT x + 1 
	FROM t 
	WHERE x < 5 
)
SELECT sum(x) FROM t;

输出结果为：

sum 
-----
	15 
(1 row)

上述例子中x从1开始，union加1后的值，循环直到x小于5 结束，之后计算x值的总和。
接着分享一个递归查询的案例，这个案例来自PostgreSQL 社区论坛一位朋友的问题，他的问题是这样的，存在一张包含如下数据的表：

 id name fatherid 
  1 中国    	0 
  2 辽宁 	1 
  3 山东 	1 
  4 沈阳 	2 
  5 大连 	2 
  6 济南 	3 
  7 和平区 	4 
  8 沈河区 	4

当给定一个id时能得到它完整的地名，例如当id=7时，地名是：中国辽宁沈阳和平区，当id=5时，地名是：中国辽宁大连，这是一个典型的层次数据递归应用场景，恰好可以通过 PostgreSQL的WITH查询实现，首先创建测试表并插入数据，如下所示：

CREATE TABLE test_area(id int4,name varchar(32),fatherid int4);

INSERT INTO test_area VALUES (1, '中国' ,0); 
INSERT INTO test_area VALUES (2, '辽宁' ,1); 
INSERT INTO test_area VALUES (3, '山东' ,1); 
INSERT INTO test_area VALUES (4, '沈阳' ,2); 
INSERT INTO test_area VALUES (5, '大连' ,2); 
INSERT INTO test_area VALUES (6, '济南' ,3); 
INSERT INTO test_area VALUES (7, '和平区' ,4); 
INSERT INTO test_area VALUES (8, '沈河区' ,4);

使用PostgreSQL的WITH查询检索ID为7以及以上的所有父节点，如下所示：

 WITH RECURSIVE r AS ( 
 		SELECT * FROM test_area WHERE id = 7 
 	UNION ALL 
 		SELECT test_area.* FROM test_area, r WHERE test_area.id = r.fatherid 
 	) 
 SELECT * FROM r ORDER BY id;

查询结果如下：

 id | name | fatherid 
 ------+-------+---------- 
  1 | 中国  |  0 
  2 | 辽宁  |  1 
  4 | 沈阳  |  2 
  7 | 和平区 |  4 
 (4 rows)

查询结果正好是ID=7节点以及它的所有父节点，接下来将输出结果的name字段合并成“中国辽宁沈阳和平区”，方法很多，这里通过string_agg函数实现，如下所示：

mydb=> WITH RECURSIVE r AS ( 
		SELECT * FROM test_area WHERE id = 7 
	UNION ALL 
		SELECT test_area.* FROM test_area, r WHERE test_area.id = r.fatherid 
		) 
SELECT string_agg(name,'') FROM ( SELECT name FROM r ORDER BY id) n; 

	string_agg 
-------------------- 
  中国辽宁沈阳和平区

以上是查找当前节点以及当前节点的所有父节点，也可以查找当前节点以及其下的所有子节点，需更改where条件，如查找沈阳市及管辖的区，代码如下所示。

mydb=> WITH RECURSIVE r AS ( 
		SELECT * FROM test_area WHERE id = 4 
	UNION ALL 
		SELECT test_area.* FROM test_area, r WHERE test_area.fatherid = r.id 
	) 
SELECT * FROM r ORDER BY id; 
id | name | fatherid 
----+--------+---------- 
 4  | 沈阳    | 2 
 7  | 和平区   | 4 
 8  | 沈河区   | 4 
(3 rows)

以上给出了CTE的两个应用场景：复杂查询中的应用和递归查询中的应用，通过示例很容易知道CTE具有以下优点：

CTE可以简化SQL代码，减少SQL嵌套层数，提高SQL 代码的可读性。
CTE的辅助语句只需要计算一次，在主查询中可以多次使用。
当不需要共享查询结果时，相比视图更轻量。

2、批量插入

批量插入是指一次性插入多条数据，主要用于提升数据插入效率，PostgreSQL有多种方法实现批量插入。

2.1 方式一：INSERT INTO…SELECT…

通过表数据或函数批量插入，语法如下：

INSERT INTO table_name SELECT...FROM source_table

比如创建一张表结构和user_ini相同的表并插入user_ini表的全量数据，代码如下所示：

mydb=> CREATE TABLE tbl_batch1(user_id int8,user_name text);
CREATE TABLE 
mydb=> INSERT INTO tbl_batch1(user_id,user_name) SELECT user_id,user_name FROM user_ini; 
INSERT 0 1000000

以上示例将表user_ini的user_id、user_name字段所有数据插入表tbl_batch1，也可以插入一部分数据，插入时指定where 条件即可。通过函数进行批量插入，如下所示：

mydb=> CREATE TABLE tbl_batch2 (id int4,info text); 
CREATE TABLE
mydb=> INSERT INTO tbl_batch2(id,info) 
	   SELECT generate_series(1,5),'batch2'; 
INSERT 0 5

通过SELECT表数据批量插入的方式大多关系型数据库都支持，接下来看看PostgreSQL支持的其他批量插入方式。

2.2 方式二：INSERT INTO VALUES（），（），…（）

PostgreSQL的另一种支持批量插入的方法为在一条 INSERT语句中通过VALUES关键字插入多条记录，通过一个例子就很容易理解，如下所示：

mydb=> CREATE TABLE tbl_batch3(id int4,info text); 
CREATE TABLE 
mydb=> INSERT INTO tbl_batch3(id,info) VALUES (1,'a'),(2,'b'),(3,'c'); 
INSERT 0 3

数据如下所示：

mydb=> SELECT * FROM tbl_batch3; 
id | info 
-------+------ 
	 1 | a 
	 2 | b 
	 3 | c 
(3 rows)

这种批量插入方式非常独特，一条SQL插入多行数据，相比一条SQL插入一条数据的方式能减少和数据库的交互，减少数据库WAL（Write-Ahead Logging）日志的生成，提升插入效率，通常很少有开发人员了解PostgreSQL的这种批量插入方式。

2.3 方式三：COPY或\COPY元命令

前面介绍了psql导入、导出表数据，使用的是COPY命令或\copy元命令，copy或\copy元命令能够将一定格式的文件数据导入到数据库中，相比INSERT命令插入效率更高，通常大数据量的文件导入一般在数据库服务端主机通过PostgreSQL 超级用户使用COPY命令导入，下面通过一个例子简单看看 COPY命令的效率，测试机为一台物理机上的虚机，配置为4核CPU，8GB内存。首先创建一张测试表并插入一千万数据，如下所示：

mydb=> CREATE TABLE tbl_batch4( id int4, info text, create_time timestamp(6) with time zone default clock_timestamp()); 
CREATE TABLE 
mydb=> INSERT INTO tbl_batch4(id,info) SELECT n,n||'_batch4' FROM generate_series(1,10000000) n; 
INSERT 0 10000000

以上示例通过INSERT插入一千万数据，将一千万数据导出到文件，如下所示：

[postgres@pghost1 ~]$ psql mydb postgres 
psql (10.0) 
Type "help" for help.
mydb=# \timing 
Timing is on. 
mydb=# COPY pguser.tbl_batch4 TO '/home/pg10/tbl_batch4.txt'; 
COPY 10000000 
Time: 6575.787 ms (00:06.576)

一千万数据导出花了6575毫秒，之后清空表tbl_batch4并将文件tbl_batch4.txt的一千万数据导入到表中，如下所示：

mydb=# TRUNCATE TABLE pguser.tbl_batch4; 
TRUNCATE TABLE 
mydb=# COPY pguser.tbl_batch4 FROM '/home/pg10/tbl_batch4.txt'; 
COPY 10000000 
Time: 15663.834 ms (00:15.664)

一千万数据通过COPY命令导入执行时间为15663毫秒。

3、RETURNING返回修改的数据

PostgreSQL的RETURNING特性可以返回DML修改的数据，具体为三个场景：INSERT语句后接RETURNING属性返回插入的数据；UPDATE语句后接RETURNING属性返回更新后的新值；DELETE语句后接RETURNING属性返回删除的数据。这个特性的优点在于不需要额外的SQL获取这些值，能够方便应用开发，下面通过示例演示。

3.1 RETURNING返回插入的数据

INSERT语句后接RETURNING属性返回插入的值，下面的代码创建测试表，并返回已插入的整行数据。

mydb=> CREATE TABLE test_r1(id serial,flag char(1)); 
CREATE TABLE 
mydb=> INSERT INTO test_r1(flag) VALUES ('a') RETURNING *; 
    id | flag 
-------+------ 
     1 | a 
(1 row) 
INSERT 0 1

RETURNING* 表示返回表插入的所有字段数据，也可以返回指定字段，RETURNING后接字段名即可，如下代码仅返回插入的id字段：

 mydb=> INSERT INTO test_r1(flag) VALUES ('b') RETURNING id; 
  id 
 ---- 
  2
(1 row) 
INSERT 0 1

3.2 RETURNING返回更新后数据

UPDATE后接RETURNING属性返回UPDATE语句更新后的值，如下所示：

mydb=> SELECT * FROM test_r1 WHERE id=1; 
 	id | flag 
-------+------ 
	 1 | a (1 row) 
mydb=> UPDATE test_r1 SET flag='p' WHERE id=1 RETURNING *; 
	id | flag 
-------+------ 
	 1 | p 
(1 row) 
UPDATE 1

3.3 RETURNING返回删除的数据

DELETE后接RETURNING属性返回删除的数据，如下所示：

mydb=> DELETE FROM test_r1 WHERE id=2 RETURNING *; 
	id | flag 
-------+------ 
	 2 | b 
(1 row) 
DELETE 1

4、UPSERT

PostgreSQL的UPSERT特性是指INSERT…ON CONFLICT UPDATE，用来解决在数据插入过程中数据冲突的情况，比如违反用户自定义约束，在日志数据应用场景中，通常会在事务中批量插入日志数据，如果其中有一条数据违反表上的约束，则整个插入事务将会回滚，PostgreSQL的UPSERT特性能解决这一问题。

4.1 UPSERT场景演示

接下来通过例子来理解UPSERT的功能，定义一张用户登录日志表并插入一条数据，如下所示：

mydb=> CREATE TABLE user_logins(user_name text primary key, login_cnt int4, last_login_time timestamp(0) without time zone); 
CREATE TABLE 
mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('francs',1); 
INSERT 0 1

在user_logins表user_name字段上定义主键，批量插入数据中如有重复会报错，如下所示：

mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('matiler',1),('francs',1); 
ERROR: duplicate key value violates unique constraint "user_logins_pkey" DETAIL: Key (user_name)=(francs) already exists.

上述SQL试图插入两条数据，其中matiler这条数据不违反主键冲突，而francs这条数据违反主键冲突，结果两条数据都不能插入。PostgreSQL的UPSERT可以处理冲突的数据，比如当插入的数据冲突时不报错，同时更新冲突的数据，如下所示：

mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('matiler',1),('francs',1) ON CONFLICT(user_name) DO UPDATE SET login_cnt=user_logins.login_cnt+EXCLUDED.login_cnt,last_login_time=now(); 
INSERT 0 2

上述INSERT语句插入两条数据，并设置规则：当数据冲突时将登录次数字段login_cnt值加1，同时更新最近登录时间 last_login_time，ON CONFLICT（user_name）定义冲突类型为 user_name字段，DO UPDATE SET是指冲突动作，后面定义了一个UPDATE语句。注意上述SET命令中引用了user_loins表和内置表EXCLUDED，引用原表user_loins访问表中已存在的冲突记录，内置表EXCLUDED引用试图插入的值，再次查询表 user_login，如下所示：

mydb=> SELECT * FROM user_logins ; 
	user_name | login_cnt | last_login_time 
--------------+-----------+--------------------- 
	  matiler | 		1 | 
	   francs | 		2 | 2021-08-08 15:23:13 
(2 rows)

一方面冲突的francs这条数据被更新了login_cnt和 last_login_time字段，另一方面新的数据matiler记录已正常插入。
也可以定义数据冲突后啥也不干，这时需指定DO NOTHING属性，如下所示：

mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('tutu',1),('francs',1) ON CONFLICT(user_name) DO NOTHING; 
INSERT 0 1

再次查询表数据，新的数据tutu这条已插入到表中，冲突的数据francs这行啥也没变，结果如下所示：

mydb=> SELECT * FROM user_logins ; 
user_name | login_cnt | last_login_time 
--------------+-----------+--------------------- 
  matiler | 		1 | 
   francs | 		2 | 2021-08-08 15:23:13 
  	 tutu |		 	1 | 
(3 rows)

4.2 UPSERT语法

PostgreSQL的UPSERT语法比较复杂，通过以上演示后再来查看语法会轻松些，语法如下：

 INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] 
 	[ ON CONFLICT [ conflict_target ] conflict_action ] 
 where conflict_target can be one of: 
 	( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] 
 	ON CONSTRAINT constraint_name 

 and conflict_action is one of: 
 	DO NOTHING 
 	DO UPDATE SET { column_name = { expression | DEFAULT } |
 		( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
 		( column_name [, ...] ) = ( sub-SELECT )
 	} [, ...]
 [ WHERE condition ]

以上语法主要注意[ON CONFLICT[conflict_target]conflict_action]这行，conflict_target指选择仲裁索引判定冲突行为，一般指定被创建约束的字段；
conflict_action指冲突动作，可以是DO NOTHING，也可以是用户自定义的UPDATE语句。

5、数据抽样

数据抽样（TABLESAMPLE）在数据处理方面经常用到，特别是当表数据量比较大时，随机查询表中一定数量记录的操作很常见，PostgreSQL早在9.5版时就已经提供了 TABLESAMPLE数据抽样功能，9.5版前通常通过ORDER BY random（）方式实现数据抽样，这种方式虽然在功能上满足随机返回指定行数据，但性能很低，如下所示：

mydb=> SELECT * FROM user_ini ORDER BY random() LIMIT 1; 
          id | user_id | user_name | create_time 
-------------+---------+-----------+------------------------------- 
	  500449 |  768810 |    2TY6P4 | 2021-08-05 15:59:32.294761+08 
(1 row) 

mydb=> SELECT * FROM user_ini ORDER BY random() LIMIT 1; 
		  id | user_id | user_name | create_time 
-------------+---------+-----------+------------------------------- 
	  324823 |  740720 |    07SKCU | 2021-08-05 15:59:29.913984+08 (1 row)

执行计划如下所示：

mydb=> EXPLAIN ANALYZE SELECT * FROM user_ini ORDER BY random() LIMIT 1; 
									QUERY PLAN 
-------------------------------------------------------------------------------- 
Limit (cost=25599.98..25599.98 rows=1 width=35) (actual time=367.867..367.868 rows=1 loops=1) 
	-> Sort (cost=25599.98..28175.12 rows=1030056 width=35) (actual time= 367.866..367.866 rows=1 loops=1) 
	   Sort Key: (random()) 
	   Sort Method: top-N heapsort Memory: 25kB 
	   		-> Seq Scan on user_ini (cost=0.00..20449.70 rows=1030056 width=35) (actual time=0.012..159.569 rows=1000000 loops=1) 
Planning time: 0.083 ms 
Execution time: 367.909 ms 
(7 rows)

表user_ini数据量为100万，从100万随机取一条上述SQL的执行时间为367ms，这种方法进行了全表扫描和排序，效率非常低，当表数据量大时，性能几乎无法接受。 9.5版本以后PostgreSQL支持TABLESAMPLE数据抽样，语法如下所示：

SELECT ... 
FROM table_name 
TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]

sampling_method指抽样方法，主要有两种：SYSTEM和 BERNOULLI，接下来详细介绍这两种抽样方式，argument指抽样百分比。
注意 explain analyze命令表示实际执行这条SQL，同时显示SQL执行计划和执行时间，Planning time表示SQL语句解析生成执行计划的时间，Execution time表示SQL的实际执行时间。

5.1 SYSTEM抽样方式

SYSTEM抽样方式为随机抽取表上数据块上的数据，理论上被抽样表的每个数据块被检索的概率是一样的，SYSTEM抽样方式基于数据块级别，后接抽样参数，被选中的块上的所有数据将被检索，下面使用示例进行说明。

创建test_sample测试表，并插入150万数据，如下所示：

mydb=> CREATE TABLE test_sample(id int4,message text,
create_time timestamp(6) without time zone default clock_timestamp()); 
CREATE TABLE 
mydb=> INSERT INTO test_sample(id,message) SELECT n, md5(random()::text) FROM generate_series(1,1500000) n; 
INSERT 0 1500000 
mydb=> SELECT * FROM test_sample LIMIT 1; 
	id | 			  message 			  |  create_time 
-------+----------------------------------+---------------------------- 
	 1 | 58f2506410be948963d6d9adf4b4e0c2 | 2021-08-08 21:17:20.984481 
(1 row)

抽样因子设置成0.01，意味着返回1500000×0.01%=150条记录，执行如下SQL：

EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE SYSTEM(0.01);

执行计划如下所示：

mydb=> EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE SYSTEM(0.01); 
						QUERY PLAN 
-------------------------------------------------------------------------------- 
Sample Scan on test_sample (cost=0.00..3.50 rows=150 width=45) (actual time=0.099..0.146 rows=107 loops=1) 
	Sampling: system ('0.01'::real) 
Planning time: 0.053 ms 
Execution time: 0.166 ms 
(4 rows)

以上执行计划主要有两点，一方面进行了Sample Scan扫描（抽样方式为SYSTEM），执行时间为0.166毫秒，性能较好，另一方面优化器预计访问150条记录，实际返回107条，为什么会返回107条记录呢？接着查看表占用的数据块数量，如下所示：

mydb=> SELECT relname,relpages FROM pg_class WHERE relname='test_sample'; relname | relpages ----------------+---------- test_sample | 14019 (1 row)

表test_sample物理上占用14019个数据块，也就是说每个数据块存储1000000/14019=107条记录。查看抽样数据的ctid，如下所示：

mydb=> SELECT ctid,* FROM test_sample TABLESAMPLE SYSTEM(0.01); ctid | id | message | create_time ------------+--------+----------------------------------+-------------------------- (5640,1) | 603481 | 385484b3452b245e46388d71ce4ea928 | 2017-08-08 21:17:23.32394 (5640,2) | 603482 | e09c526118f1d4b3c391d59ae915c4e8 | 2017-08-08 21:17:23.323964 ….省略很多行 (5640,107) | 603587 | c33875a052f4ca63c4b38c649fb6bcc3 | 2017-08-08 21:17:23.324336 (107 rows)

ctid是表的隐藏列，括号里的第一位表示逻辑数据块编号，第二位表示逻辑块上的数据的逻辑编号，从以上看出，这 107条记录都存储在逻辑编号为5640的数据块上，也就是说抽样查询返回了一个数据块上的所有数据，抽样因子固定为 0.01，多次执行以下查询，如下所示：

mydb=> SELECT count(*) FROM test_sample TABLESAMPLE SYSTEM(0.01); count ------- 214 (1 row) 
mydb=> SELECT count(*) FROM test_sample TABLESAMPLE SYSTEM(0.01); count ------- 107
(1 row)

再次查询发现返回的记录为214或107，由于一个数据块存储107条记录，因此查询结果有时返回了两个数块上的所有数据，这是因为抽样因子设置成0.01，意味着返回 1500000×0.01%=150条记录，150条记录需要两个数据块存储，这也验证了SYSTEM抽样方式返回的数据以数据块为单位，被抽样的块上的所有数据被检索。

5.2 BERNOULLI抽样方式

BERNOULLI抽样方式随机抽取表的数据行，并返回指定百分比数据，BERNOULLI抽样方式基于数据行级别，理论上被抽样表的每行记录被检索的概率是一样的，因此 BERNOULLI抽样方式抽取的数据相比SYSTEM抽样方式具有更好的随机性，但性能上相比SYSTEM抽样方式低很多，下面演示下BERNOULLI抽样方式，同样基于test_sample测试表。设置抽样方式为BERNOULLI，抽样因子为0.01，如下所示：

mydb=> EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE BERNOULLI (0.01); 
								QUERY PLAN 
-------------------------------------------------------------------------------- 
Sample Scan on test_sample (cost=0.00..14020.50 rows=150 width=45) (actual time=0.025..22.541 rows=152 loops=1) 
	Sampling: bernoulli ('0.01'::real) 
Planning time: 0.063 ms 
Execution time: 22.569 ms 
(4 rows)

从以上执行计划看出进行了Sample Scan扫描（抽样方式为BERNOULLI），执行计划预计返回150条记录，实际返回152条，从返回的记录数来看，非常接近150条（1000000×0.01%），但执行时间却要22.569毫秒，性能相比 SYSTEM抽样方式0.166毫秒差了136倍。多次执行以下查询，查看返回记录数的变化，如下所示：

mydb=> SELECT count(*) FROM test_sample TABLESAMPLE BERNOULLI(0.01); 
count 
------- 
151 
(1 row) 
mydb=> SELECT count(*) FROM test_sample TABLESAMPLE BERNOULLI(0.01); 
count 
------- 
147 
(1 row)

从以上看出，BERNOULLI抽样方式返回的数据量非常接近抽样数据的百分比，而SYSTEM抽样方式数据返回以数据块为单位，被抽样的块上的所有数据都被返回，因此SYSTEM抽样方式返回的数据量偏差较大。由于BERNOULLI抽样基于数据行级别，猜想返回的数据应该位于不同的数据块上，通过查询表的ctid进行验证，如下所示：

mydb=> SELECT ctid,id,message FROM test_sample TABLESAMPLE BERNOULLI(0.01) lIMIT 3; 
      ctid |   id  |  message 
-----------+-------+---------------------------------- 
   (55,30) |  5915 | f3803f234f6cf6cdd276d9d027487582 
  (240,23) | 25703 | c04af69ac76f6465832e0cd87939a1af 
   (318,3) | 34029 | dd35438b24980d1a8ed2d3f5edd5ca1c

从以上三条记录的ctid信息看出，三条数据分别位于数据块55、240、318上，因此BERNOULLI抽样方式随机性相比 SYSTEM抽样方式更好。

5.3 小结

本节演示了SYSTEM和BERNOULLI抽样方式，SYSTEM 抽样方式基于数据块级别，随机抽取表数据块上的记录，因此这种方式抽取的记录的随机性不是很好，但返回的数据以数据块为单位，抽样性能很高，适用于抽样效率优先的场景，例如抽样大小为上百GB的日志表；而BERNOULLI抽样方式基于数据行，相比SYSTEM抽样方式所抽样的数据随机性更好，但性能相比SYSTEM差很多，适用于抽样随机性优先的场景。

玻璃酸钠滴眼液

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
PG系列5-SQL高级特性1（with查询，批量插入，数据抽样...）

文章目录1、WITH查询2、批量插入3、RETURNING返回修改的数据4、UPSERT5、数据抽样6、聚合函数7、窗口函数1、WITH查询2、批量插入3、RETURNING返回修改的数据4、UPSERT5、数据抽样6、聚合函数7、窗口函数...
复制链接

扫一扫