在PostgreSQL中，如何操作多个子查询的执行顺序

Question

我对PostgreSQL有一个问题，它运行了很长时间。这是一个示例代码。

create table t1 (
cust_id int,
cust_name varchar(100),
comment char(100));
insert into t1 
select i, 'TESTNAME'||i, 'dummyy' from generate_series(1,1000) a(i);
select * from t1;
create table t2
(id int, cust_id int, amount bigint);
insert into t2
select i, case when i < 10 then i else i+1000 end, i*10
from generate_series(1,100) a(i);
select * from t2; 
create table t3(
singo_id int, cust_id int, reg_date date, comment char(200));
insert into t3
select i, mod(i,1000), '2021-01-01'::date + mod(i,1000), 'dummyyyy'
from generate_series(1,2000) a(i);
--I inserted 'offset' to prevent subquery collapse on purpose.
select count(*)
from t1 a
where exists (select 1
from t3 b
where reg_date >= '2021-02-02'
and a.cust_id = b.cust_id
offset 0)
and exists (select 1
from t2 c
where a.cust_id = c.cust_id
offset 0);

--执行计划

| Aggregate (actual time=8.047..8.048 rows=1 loops=1)                                  |
|   Buffers: shared hit=1568                                                           |
|   ->  Seq Scan on t1 a (actual time=8.042..8.043 rows=0 loops=1)                     |
|         Filter: ((SubPlan 2) AND (SubPlan 1))                                        |
|         Rows Removed by Filter: 1000                                                 |
|         Buffers: shared hit=1568                                                     |
|         SubPlan 2                                                                    |
|           ->  Seq Scan on t2 c (actual time=0.005..0.005 rows=0 loops=1000)          |
|                 Filter: (a.cust_id = cust_id)                                        |
|                 Rows Removed by Filter: 99                                           |
|                 Buffers: shared hit=1000                                             |
|         SubPlan 1                                                                    |
|           ->  Seq Scan on t3 b (actual time=0.293..0.293 rows=0 loops=9)             |
|                 Filter: ((reg_date >= '2021-02-02'::date) AND (a.cust_id = cust_id)) |
|                 Rows Removed by Filter: 2000                                         |
|                 Buffers: shared hit=549  

我知道我做的SQL测试很愚蠢。我在生产系统中的真实SQL非常复杂，并且子查询无法折叠。从上面的执行计划来看，PostgreSQL似乎先用t2表过滤t1表。我希望优化器首先强制优化器使用t3表进行筛选。我该怎么做？我在下面更改了测试SQL。但它没有起作用。

select count(*)
from t1 a
where exists (select 1
from t2 c
where a.cust_id = c.cust_id
offset 0)
and exists (select 1
from t3 b
where reg_date >= '2021-02-02'
and a.cust_id = b.cust_id
offset 0);

Answer 1

没有办法强制执行订单-PostgreSQL将首先执行它认为在减少行数方面更便宜或更有效的子计划。

您可以使用一个物化的公共表表达式：

WITH subq AS MATERIALIZED (
SELECT a.id, a.cust_id
FROM t1 AS a
WHERE EXISTS (SELECT 1 FROM t3 AS b
WHERE b.reg_date >= '2021-02-02'
AND a.cust_id = b.cust_id)
)
SELECT count(*)
FROM subq
WHERE EXISTS (SELECT 1 FROM t2 AS c
WHERE subq.cust_id = c.cust_id);

对于12之前的PostgreSQL版本，请省略MATERIALIZED。