Postgresql上的执行计划不正确

Question

我正在尝试从SQL Server迁移到Postgresql。这是我的Posgresql代码：

Create View person_names As
SELECT lp."Code", n."Name", n."Type"
from "Persons" lp
Left Join LATERAL
(
Select *
From "Names" n
Where n.id = lp.id
Order By "Date" desc
Limit 1
) n on true
limit 100;
Explain
Select "Code" From person_names;

它打印

"Subquery Scan on person_names  (cost=0.42..448.85 rows=100 width=10)"
"  ->  Limit  (cost=0.42..447.85 rows=100 width=56)"
"        ->  Nested Loop Left Join  (cost=0.42..303946.91 rows=67931 width=56)"
"              ->  Seq Scan on ""Persons"" lp  (cost=0.00..1314.31 rows=67931 width=10)"
"              ->  Limit  (cost=0.42..4.44 rows=1 width=100)"
"                    ->  Index Only Scan Backward using ""IX_Names_Person"" on ""Names"" n  (cost=0.42..4.44 rows=1 width=100)"
"                          Index Cond: ("id" = (lp."id")::numeric)"

为什么有一个"；"仅索引扫描"；对于"；名称"；桌子不需要此表即可获得结果。在SQL Server上；"人"；桌子如何调整Postgres以获得更好的查询计划？我正在尝试最新版本，它是Postgresql15beta 3。

这是SQL Server版本：

Create View person_names As
SELECT top 100 lp."Code", n."Name", n."Type"
from "Persons" lp
Outer Apply
(
Select Top 1 *
From "Names" n
Where n.id = lp.id
Order By "Date" desc
) n
GO
SET SHOWPLAN_TEXT ON;
GO
Select "Code" From person_names;

它给出了正确的执行计划：

|--Top(TOP EXPRESSION:((100)))
|--Index Scan(OBJECT:([Persons].[IX_Persons] AS [lp]))

Answer 1

将横向联接更改为常规的左联接，然后Postgres可以删除Names表上的select：

create View person_names 
As
SELECT lp.Code, n.Name, n.Type
from Persons lp
Left Join (
Select distinct on (id) *
From Names n
Order By id, Date desc
) n on n.id = lp.id
limit 100;

如果确实包含Names表中的列，则以下索引将支持distinct on ()：

create index on "Names"(id, "Date" desc);

对于select code from names，这给了我这个计划：

QUERY PLAN                                                                                                  
------------------------------------------------------------------------------------------------------------
Seq Scan on persons lp  (cost=0.00..309.00 rows=20000 width=7) (actual time=0.009..1.348 rows=20000 loops=1)
Planning Time: 0.262 ms                                                                                     
Execution Time: 1.738 ms                                                                                    

对于select Code, name, type From person_names;，这给了我这个计划：

QUERY PLAN                                                                                                                                           
-----------------------------------------------------------------------------------------------------------------------------------------------------
Hash Right Join  (cost=559.42..14465.93 rows=20000 width=25) (actual time=5.585..68.545 rows=20000 loops=1)                                          
Hash Cond: (n.id = lp.id)                                                                                                                          
->  Unique  (cost=0.42..13653.49 rows=20074 width=26) (actual time=0.053..57.323 rows=20000 loops=1)                                               
->  Index Scan using names_id_date_idx on names n  (cost=0.42..12903.49 rows=300000 width=26) (actual time=0.052..41.125 rows=300000 loops=1)
->  Hash  (cost=309.00..309.00 rows=20000 width=11) (actual time=5.407..5.407 rows=20000 loops=1)                                                  
Buckets: 32768  Batches: 1  Memory Usage: 1116kB                                                                                             
->  Seq Scan on persons lp  (cost=0.00..309.00 rows=20000 width=11) (actual time=0.011..2.036 rows=20000 loops=1)                            
Planning Time: 0.460 ms                                                                                                                              
Execution Time: 69.180 ms                                                                                                                            

当然，我不得不猜测表的结构，因为您还没有提供任何DDL。

在线示例

Answer 2

像那样更改视图定义

create view person_names as
select p."Code",
(select "Name"
from "Names" n
where n.id = p.id
order by "Date" desc
limit 1)
from "Persons" p
limit 100;