在下面的示例中，我将在两个列上连接两个相同的表：

create table a (id int,txt text);
create table b (id int,txt text);
insert into a select *,* from generate_series(1,40000);
insert into b select *,* from generate_series(1,40000);
analyze a;
analyze b;
explain analyze 
select * from a inner join b on a.id = b.id and a.txt = b.txt;

在解释计划中，您可以看到它低估了连接产生的行数约40.000。它认为产生了1行，而不是40.000行。在我的真实的示例中（此理论示例基于此示例），这是一个问题，因为对行数的严重错误估计会导致包含此连接的较大查询的执行计划出现错误：

Hash Join  (... rows=1 ...) (actual ... rows=40000 ...)

因此，很明显，计划器并不知道对于表a中的每一行，它都将在表b中找到一行。很清楚，应该怎么做呢？有两个解决方法：
(A)左连接
使用左联接，我们可以更正估计值：

explain analyze 
select * from a LEFT join b on a.id = b.id and a.txt = b.txt;

我们现在可以看到估计是正确的：

Hash Left Join  (... rows=40000 ...) (actual ... rows=40000 ...)

(B)外部索引键
使用外键，我们还可以更正估计值：

CREATE UNIQUE INDEX unq_b ON b USING btree (id,txt);
alter table a add constraint fk_a foreign key (id,txt) references b (id,txt);
explain analyze 
select * from a inner join b on a.id = b.id and a.txt = b.txt;

我们现在可以看到估计是正确的：

Hash Join  (... rows=40000 ...) (actual ... rows=40000 ...)

我也不想使连接成为左连接，因为我不能保证查询结果在所有边缘情况下都与以前100%相同。我也不想引入FK，因为程序会以各种顺序插入表中，我将不得不更改应用程序。
你能想出其他方法来告诉计划者这两个表的特殊关系吗？也许是一种编写查询的特殊方法？或者是某种统计对象？有什么想法吗？
TYVM！
这在两个版本上进行了测试：

PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
PostgreSQL 14.6, compiled by Visual C++ build 1914, 64-bit

UPDATE -此错误估计成为问题的原因示例：

在我的真实的例子中，postgres认为连接中只输出了1行，而实际上输出了40.000行，这是有问题的。这是因为它随后决定对1行（实际上是40.000行）进行嵌套循环，并在一个大表上使用FTS--因此在一个大表上使用40.000 FTS：

create table c (id int,txt text);
insert into c select *,* from generate_series(1,40000000);
analyze c;
SET max_parallel_workers_per_gather = 0;
set join_collapse_limit = 1;
explain
with a_b as (
    select a.id a_id,b.id b_id,a.txt a_txt,b.txt b_txt 
    from a inner join b 
    on a.id = b.id and a.txt = b.txt
)
select * from a_b inner join c 
on a_b.a_id = c.id and a_b.b_txt = c.txt and a_b.b_id = c.id and a_b.a_txt = c.id::text;

即表c中的40.000 FTS：

QUERY PLAN                                                             |
-----------------------------------------------------------------------+
Nested Loop  (cost=1216.00..921352.51 rows=1 width=30)                 |
  Join Filter: ((a.id = c.id) AND (a.txt = c.txt))                     |
  ->  Hash Join  (cost=1216.00..2132.01 rows=1 width=18)               |
        Hash Cond: ((a.id = b.id) AND (a.txt = b.txt))                 |
        ->  Seq Scan on a  (cost=0.00..616.00 rows=40000 width=9)      |
        ->  Hash  (cost=616.00..616.00 rows=40000 width=9)             |
              ->  Seq Scan on b  (cost=0.00..616.00 rows=40000 width=9)|
  ->  Seq Scan on c  (cost=0.00..916220.48 rows=200001 width=12)       |
        Filter: (txt = (id)::text)                                     |

有趣的是，左连接技巧在这里甚至不起作用，只有FK修正了估计值，从而修正了计划：

/* left join trick not working*/ 
explain
with a_b as (
    select a.id a_id,b.id b_id,a.txt a_txt,b.txt b_txt 
    from a LEFT join b 
    on a.id = b.id and a.txt = b.txt
)
select * from a_b inner join c 
on a_b.a_id = c.id and a_b.b_txt = c.txt and a_b.b_id = c.id and a_b.a_txt = c.id::text;
/*QUERY PLAN                                                           |
-----------------------------------------------------------------------+
Nested Loop  (cost=1216.00..921352.51 rows=1 width=30)                 |
  Join Filter: ((a.id = c.id) AND (a.txt = c.txt))                     |
  ->  Hash Join  (cost=1216.00..2132.01 rows=1 width=18)               |
        Hash Cond: ((a.id = b.id) AND (a.txt = b.txt))                 |
        ->  Seq Scan on a  (cost=0.00..616.00 rows=40000 width=9)      |
        ->  Hash  (cost=616.00..616.00 rows=40000 width=9)             |
              ->  Seq Scan on b  (cost=0.00..616.00 rows=40000 width=9)|
  ->  Seq Scan on c  (cost=0.00..916220.48 rows=200001 width=12)       |
        Filter: (txt = (id)::text)                                     |*/
/* with the FK the plan is correct */ 
CREATE UNIQUE INDEX unq_b ON b USING btree (id,txt);
alter table a add constraint fk_a foreign key (id,txt) references b (id,txt); 
        
explain
with a_b as (
    select a.id a_id,b.id b_id,a.txt a_txt,b.txt b_txt 
    from a join b 
    on a.id = b.id and a.txt = b.txt
)
select * from a_b inner join c 
on a_b.a_id = c.id and a_b.b_txt = c.txt and a_b.b_id = c.id and a_b.a_txt = c.id::text;
/*QUERY PLAN                                                                   |
-----------------------------------------------------------------------------+
Hash Join  (cost=2642.00..920362.50 rows=1 width=30)                         |
  Hash Cond: ((c.id = a.id) AND (c.txt = a.txt))                             |
  ->  Seq Scan on c  (cost=0.00..916220.48 rows=200001 width=12)             |
        Filter: (txt = (id)::text)                                           |
  ->  Hash  (cost=2042.00..2042.00 rows=40000 width=18)                      |
        ->  Hash Join  (cost=1216.00..2042.00 rows=40000 width=18)           |
              Hash Cond: ((a.id = b.id) AND (a.txt = b.txt))                 |
              ->  Seq Scan on a  (cost=0.00..616.00 rows=40000 width=9)      |
              ->  Hash  (cost=616.00..616.00 rows=40000 width=9)             |
                    ->  Seq Scan on b  (cost=0.00..616.00 rows=40000 width=9)|*/

此示例所基于的真实的示例的执行计划的屏幕快照（绿色箭头显示问题）。请注意，真实示例连续两次出现1：1问题（2个FK可以解决此问题）：