In PostgreSQL how can I manipulate multiple subquery execution order?

Question

I have a problem with PostgreSQL, which has a long elapsed time. Here is a sample code.

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);

--execution plan

| 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

I know that the test SQL I made is silly. My real SQL in a production system is very complicated and the subqueries cannot be collapsed. Looking at the execution plan above, it seems that PostgreSQL filters t1 table with t2 table first. What I want the optimizer to do is to force the optimizer to filter with t3 table first. How can I do that? I changed the test SQL to this below. But it didn't work.

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);

user_6464308 · Answer

There is no way to force the order - PostgreSQL will execute that subplan first that it thinks is cheaper or more effective in reducing the row count.

What you can do is to use a materialized common table expression:

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);

With PostgreSQL versions before 12, omit the MATERIALIZED.

In PostgreSQL how can I manipulate multiple subquery execution order?

1 Answers