在使用 PostgreSQL 的时候，我肯定有过一段时间渴望一个带有特性的or语句。虽然 SQL 标准本身在 SQL:2016 的问题上没有发言权，但存在支持这一点的现有 SQL 数据库方言的明确案例。 UPDATE DELETE LIMIT
可悲的是，如果你尝试在 PostgreSQL 中做这样的事情，结果是：
错误：“LIMIT”第1 行或附近的语法错误：DELETE FROM big_table LIMIT 10000；
用例
在我们深入研究细节之前，让我们看一下此类功能的一些用例。出于多种原因，这种行为的主要愿望是将大型交易分解为较小的交易：
批处理和 UPDATE DELETE
查询旨在运行完成，但出于性能或其他原因，我们希望以块的形式而不是在一个大型事务中重复发出操作。这可以最大限度地减少锁持有或争用，防止不必要的表膨胀，或以其他方式限制对整体系统资源的影响。
更好的磁盘空间使用，包括表数据和 WAL
如果您在一个事务中就地放置一个大表，则数据库将不得不保留所有旧行版本，以防事务回滚或失败。因此，您实际上将数据库更改的磁盘大小增加了一倍。将它分成多个语句，如果穿插有. UPDATE UPDATE LIMIT VACUUM
对于某些数据库设置，以块为单位执行这样的操作更有意义，因为进行所有这些更改会增加 WAL 开销；间隔操作有时对于在备份系统、副本等上分配负载很有用。
影响行的任意子集
您可能希望使用所有总行的子集来处理某些操作（特别是），而不完全考虑您正在操作的特定行。例如，您可能有一个表，希望从中提取任意数量的行并在查询的其他部分中使用，例如使用虚构语法。 DELETE DELETE FROM foo ORDER BY random() LIMIT 10 RETURNING *
在不影响整个表的情况下将行迁移到新分区
特别是，当将现有表数据移动到分区时，您需要将这些操作分成块。当您将数据移动到新分区时，查询返回的数据集将继续保持不变，但是，如果您天真地发出并在表中的所有行上，这将在所需的事务期间暂停对此数据的查询移动有问题的数据，以及具有相同的基于空间的约束，将旧数据保留在旧表中，直到操作和最新完成。pg_partman使用这种方法来填充表数据。 INSERT DELETE VACUUM
一句警告：
在一些定义不明确的标准上操作（特别是对于破坏性操作）是有目的的困难。通常，强烈建议将该子句用于任何用于可变查询的操作。 ORDER BY LIMIT
让我们试试看：
因此，让我们在这里尝试一个基本示例：
CREATE TABLE big_table (id INT PRIMARY KEY, data TEXT, process_time TIMESTAMPTZ);
INSERT INTO big_table (id, data) SELECT v, 'Foo'||v FROM generate_series(1,100000) v;
Table "public.big_table"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
id | integer | | not null |
data | text | | |
process_time | timestamp with time zone | | |
Indexes:
"big_table_pkey" PRIMARY KEY, btree (id)
验证一些数据是否存在：
SELECT * FROM big_table ORDER BY id LIMIT 10;
id data process_time 1 Foo1 2 Foo2 3 Foo3 4 Foo4 5 Foo5 6 Foo6 7 Foo7 8 Foo8 9 Foo9 10 Foo10
CTE 来救援！
由于肯定能够提供我们想要的那种限制，那么显然这里的答案是能够使用我个人最喜欢的工具之一公共表表达式(CTE)将有问题的or语句转换为。 SELECT DELETE UPDATE SELECT
使用这种方法，我们通常可以构造我们的or查询，首先使用 a来定义受影响的行，然后对其执行相关的底层操作；基本上看起来像这个一般食谱： DELETE UPDATE SELECT
WITH rows AS (
SELECT
something
FROM
big_table
LIMIT 10
)
DELETE FROM big_table
WHERE something IN (SELECT something FROM rows)
;
那么这里所指的是什么？如果表有主键，这是最直接的使用这个数据的方式，实际上会有这样的计划： something
EXPLAIN WITH rows AS (
SELECT
id
FROM
big_table
LIMIT 10
)
DELETE FROM big_table
WHERE id IN (SELECT id FROM rows)
;
这是这个查询计划：
Delete on big_table (cost=0.60..83.49 rows=0 width=0)
-> Nested Loop (cost=0.60..83.49 rows=10 width=34)
-> HashAggregate (cost=0.31..0.41 rows=10 width=32)
Group Key: rows.id
-> Subquery Scan on rows (cost=0.00..0.29 rows=10 width=32)
-> Limit (cost=0.00..0.19 rows=10 width=4)
-> Seq Scan on big_table big_table_1 (cost=0.00..1152.33 rows=61133 width=4)
-> Index Scan using big_table_pkey on big_table (cost=0.29..8.31 rows=1 width=10)
Index Cond: (id = rows.id)
同样的事情，但使用连接条件而不是构造： IN(…)
EXPLAIN WITH rows AS (
SELECT
id
FROM
big_table
LIMIT 10
)
DELETE FROM big_table
WHERE EXISTS (SELECT * FROM rows WHERE rows.id = big_table.id)
;
Delete on big_table (cost=0.60..83.49 rows=0 width=0)
-> Nested Loop (cost=0.60..83.49 rows=10 width=34)
-> HashAggregate (cost=0.31..0.41 rows=10 width=32)
Group Key: rows.id
-> Subquery Scan on rows (cost=0.00..0.29 rows=10 width=32)
-> Limit (cost=0.00..0.19 rows=10 width=4)
-> Seq Scan on big_table big_table_1 (cost=0.00..1152.33 rows=61133 width=4)
-> Index Scan using big_table_pkey on big_table (cost=0.29..8.31 rows=1 width=10)
Index Cond: (id = rows.id)
并且条件包含在 CTE 中： ORDER BY
EXPLAIN ANALYZE WITH rows AS (
SELECT
id
FROM
big_table
ORDER BY id
LIMIT 10
)
DELETE FROM big_table
USING rows WHERE big_table.id = rows.id
;
SELECT COUNT(*) FROM big_table;
Delete on big_table (cost=0.58..84.15 rows=0 width=0) (actual time=0.209..0.212 rows=0 loops=1)
-> Nested Loop (cost=0.58..84.15 rows=10 width=34) (actual time=0.091..0.152 rows=10 loops=1)
-> Subquery Scan on rows (cost=0.29..1.07 rows=10 width=32) (actual time=0.072..0.087 rows=10 loops=1)
-> Limit (cost=0.29..0.97 rows=10 width=4) (actual time=0.049..0.057 rows=10 loops=1)
-> Index Only Scan using big_table_pkey on big_table big_table_1 (cost=0.29..4185.28 rows=61133 width=4) (actual time=0.047..0.053 rows=10 loops=1)
Heap Fetches: 10
-> Index Scan using big_table_pkey on big_table (cost=0.29..8.31 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=10)
Index Cond: (id = rows.id)
Planning Time: 0.835 ms
Execution Time: 0.338 ms
99990
UPDATE 声明
让我们来看看这如何影响语句： UPDATE
EXPLAIN ANALYZE WITH rows AS (
SELECT
id
FROM
big_table
ORDER BY id
LIMIT 10
)
UPDATE big_table
SET process_time = now()
WHERE EXISTS (SELECT * FROM rows WHERE big_table.id = rows.id)
;
Update on big_table (cost=1.39..84.30 rows=0 width=0) (actual time=0.314..0.317 rows=0 loops=1)
-> Nested Loop (cost=1.39..84.30 rows=10 width=42) (actual time=0.098..0.148 rows=10 loops=1)
-> HashAggregate (cost=1.10..1.20 rows=10 width=32) (actual time=0.079..0.085 rows=10 loops=1)
Group Key: rows.id
Batches: 1 Memory Usage: 24kB
-> Subquery Scan on rows (cost=0.29..1.07 rows=10 width=32) (actual time=0.055..0.066 rows=10 loops=1)
-> Limit (cost=0.29..0.97 rows=10 width=4) (actual time=0.036..0.042 rows=10 loops=1)
-> Index Only Scan using big_table_pkey on big_table big_table_1 (cost=0.29..4185.28 rows=61133 width=4) (actual time=0.034..0.039 rows=10 loops=1)
Heap Fetches: 20
-> Index Scan using big_table_pkey on big_table (cost=0.29..8.31 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=10)
Index Cond: (id = rows.id)
Planning Time: 0.773 ms
Execution Time: 0.461 ms
SELECT COUNT(*) FROM big_table WHERE process_time IS NOT NULL;
count 10
在此选择中包含随机性
好的，如果您想使用具有指定顺序索引的表，那很好，但是如果我们想选择任意记录呢？
这种情况下的简单解决方案（在某些性能损失下）是向内部CTE添加。我们还通过使用语句的子句来最小化此处考虑的行数，以将此处随机查看的行数减少到表的 1%。（有关更多解释/详细信息，请参阅SELECT 文档页面上的 TABLESAMPLE。） ORDER BY RANDOM() SELECT TABLESAMPLE SELECT
EXPLAIN ANALYZE WITH rows AS (
SELECT
id
FROM
big_table TABLESAMPLE bernoulli(1)
ORDER BY RANDOM()
LIMIT 10
)
UPDATE big_table
SET process_time = now()
WHERE EXISTS (SELECT * FROM rows WHERE big_table.id = rows.id)
;
Update on big_table (cost=562.38..645.29 rows=0 width=0) (actual time=6.002..6.004 rows=0 loops=1)
CTE rows
-> Limit (cost=561.84..561.87 rows=10 width=12) (actual time=5.637..5.639 rows=10 loops=1)
-> Sort (cost=561.84..563.37 rows=611 width=12) (actual time=5.635..5.636 rows=10 loops=1)
Sort Key: (random())
Sort Method: top-N heapsort Memory: 26kB
-> Sample Scan on big_table big_table_1 (cost=0.00..548.64 rows=611 width=12) (actual time=0.064..5.346 rows=973 loops=1)
Sampling: bernoulli ('1'::real)
-> Nested Loop (cost=0.52..83.43 rows=10 width=42) (actual time=5.700..5.781 rows=10 loops=1)
-> HashAggregate (cost=0.23..0.33 rows=10 width=32) (actual time=5.677..5.684 rows=10 loops=1)
Group Key: rows.id
Batches: 1 Memory Usage: 24kB
-> CTE Scan on rows (cost=0.00..0.20 rows=10 width=32) (actual time=5.656..5.664 rows=10 loops=1)
-> Index Scan using big_table_pkey on big_table (cost=0.29..8.31 rows=1 width=10) (actual time=0.008..0.008 rows=1 loops=10)
Index Cond: (id = rows.id)
Planning Time: 1.074 ms
Execution Time: 6.176 ms
没有PK怎么办？
到目前为止，这种方法取决于在表上具有主键，但是您可能希望为没有 PK 的表处理此问题。该怎么办？
好吧，在这种情况下使用 PK 的目的是唯一标识我们想要定位的相关行/元组。您可能知道也可能不知道，PostgreSQL 在所有表上都有一个隐藏的系统列，称为 ，用于标识磁盘上的显式元组；这是一个索引列（以值本身快速唯一标识特定行的确切表块和元组 ID 的方式），因此就我们的目的而言，它的功能与主键完全相同。 ctid
让我们测试一下：
CREATE TABLE another_table (id INT, data TEXT, process_time TIMESTAMPTZ);
INSERT INTO another_table (id, data) SELECT v, 'Foo'||v FROM generate_series(1,100000) v;
请注意，这与之前的表定义完全相同，只是您没有创建主键，因此表上没有索引：
Table "public.another_table"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
id | integer | | |
data | text | | |
process_time | timestamp with time zone | | |
让我们尝试我们之前在没有更改的情况下执行的初始查询： ctid
EXPLAIN WITH rows AS (
SELECT
id
FROM
another_table
ORDER BY id
LIMIT 10
)
UPDATE another_table
SET process_time = now()
WHERE EXISTS (SELECT * FROM rows WHERE another_table.id = rows.id)
;
Update on another_table (cost=2473.64..3828.10 rows=0 width=0)
-> Hash Semi Join (cost=2473.64..3828.10 rows=3057 width=42)
Hash Cond: (another_table.id = rows.id)
-> Seq Scan on another_table (cost=0.00..1152.33 rows=61133 width=10)
-> Hash (cost=2473.52..2473.52 rows=10 width=32)
-> Subquery Scan on rows (cost=2473.39..2473.52 rows=10 width=32)
-> Limit (cost=2473.39..2473.42 rows=10 width=4)
-> Sort (cost=2473.39..2626.22 rows=61133 width=4)
Sort Key: another_table_1.id
-> Seq Scan on another_table another_table_1 (cost=0.00..1152.33 rows=61133 width=4)
那个查询计划很糟糕（正如没有索引或主键的表所期望的那样）。让我们看看使用： ctid
EXPLAIN WITH rows AS (
SELECT
ctid
FROM
another_table
ORDER BY ctid
LIMIT 10
)
UPDATE another_table
SET process_time = now()
FROM rows
WHERE another_table.ctid = rows.ctid
;
Update on another_table (cost=2473.39..2513.77 rows=0 width=0)
-> Nested Loop (cost=2473.39..2513.77 rows=10 width=44)
-> Subquery Scan on rows (cost=2473.39..2473.52 rows=10 width=36)
-> Limit (cost=2473.39..2473.42 rows=10 width=6)
-> Sort (cost=2473.39..2626.22 rows=61133 width=6)
Sort Key: another_table_1.ctid
-> Seq Scan on another_table another_table_1 (cost=0.00..1152.33 rows=61133 width=6)
-> Tid Scan on another_table (cost=0.00..4.01 rows=1 width=6)
TID Cond: (ctid = rows.ctid)
请注意，此特定示例实际上仅适用于我们不关心所选项目的顺序的情况，因为我们仍然需要使用未索引的字段进行排序。这个故事的寓意：仅对索引字段执行此操作。
概括
除非并且直到 PostgreSQL 支持和with ，在纯 SQL 中最合适的解决方法是使用 CTE 方法。 UPDATE DELETE LIMIT
