如何用LINQ生成PostgreSQL的LATERAL左外联查询优化性能？

问题场景 #

使用LINQ查询时遭遇性能瓶颈，调试生成的SQL后发现：

• 最初的写法会全量扫描documenthistory表，性能低下
• 修改后的写法虽有提升，但生成的SQL包含大量重复子查询，结构冗余

最初的LINQ写法 #

from doc in context.document
from lasthistory in context.documenthistory
    .Where(x => x.documentid == doc.id)
    .OrderByDescending(x => x.actiondatetime)
    .Take(1)
    .DefaultIfEmpty()
where lasthistory.actiondatetime >= periodFrom
    && lasthistory.actiondatetime < periodTo.AddDays(1)
select new
{
    id = doc.id,
    lastactionby = lasthistory.actionby,
    lastactiondatetime = lasthistory.actiondatetime
}

对应的低效SQL：

SELECT d.id, t0.actionby AS lastactionby, t0.actiondatetime AS lastactiondatetime
FROM dbo.document AS d
LEFT JOIN (
    SELECT t.actionby, t.actiondatetime, t.documentid
    FROM (
        SELECT d0.actionby, d0.actiondatetime, d0.documentid, ROW_NUMBER() OVER(PARTITION BY d0.documentid ORDER BY d0.actiondatetime DESC) AS row
        FROM dbo.documenthistory AS d0
    ) AS t
    WHERE t.row <= 1
) AS t0 ON d.id = t0.documentid
WHERE (t0.actiondatetime >= @__periodFrom_1) AND (t0.actiondatetime < @__AddDays_2)

修改后的LINQ写法 #

from doc in context.document
let lasthistory = context.documenthistory
    .Where(x => x.documentid == doc.id)
    .OrderByDescending(x => x.actiondatetime)
    .FirstOrDefault()
where lasthistory.actiondatetime >= periodFrom
    && lasthistory.actiondatetime < periodTo.AddDays(1)
select new
{
    id = doc.id,
    lastactionby = lasthistory.actionby,
    lastactiondatetime = lasthistory.actiondatetime
}

对应的冗余SQL：

SELECT d.id, 
    (
        SELECT d2.actionby 
        FROM dbo.documenthistory AS d2 
        WHERE d2.documentid = d.id 
        ORDER BY d2.actiondatetime DESC 
        LIMIT 1
    ) AS lastactionby, 
    (
        SELECT d3.actiondatetime 
        FROM dbo.documenthistory AS d3 
        WHERE d3.documentid = d.id 
        ORDER BY d3.actiondatetime DESC 
        LIMIT 1
    ) AS lastactiondatetime
FROM dbo.document AS d
WHERE ((SELECT d0.actiondatetime FROM dbo.documenthistory AS d0 WHERE d0.documentid = d.id ORDER BY d0.actiondatetime DESC LIMIT 1) >= @__periodFrom_1)
    AND ((SELECT d1.actiondatetime FROM dbo.documenthistory AS d1 WHERE d1.documentid = d.id ORDER BY d1.actiondatetime DESC LIMIT 1) < @__AddDays_2)

期望生成的PostgreSQL查询 #

期望使用LATERAL左连接减少冗余计算，提升性能：

SELECT d.id, t.actionby AS lastactionby, t.actiondatetime AS lastactiondatetime
FROM dbo.document AS d
LEFT JOIN LATERAL (
    SELECT d0.actionby, d0.actiondatetime
    FROM dbo.documenthistory AS d0
    WHERE d0.documentid = d.id
    ORDER BY d0.actiondatetime DESC
    FETCH FIRST 1 ROW ONLY
) t ON true
WHERE (t.actiondatetime >= @__periodFrom_1) AND (t.actiondatetime < @__AddDays_2)

可行解决方案 #

1. 适配EF Core 3.0+的LINQ写法 #

以下写法会生成你期望的LATERAL左连接查询，避免全量扫描和重复子查询：

from doc in context.document
from lasthistory in context.documenthistory
    .Where(x => x.documentid == doc.id)
    .OrderByDescending(x => x.actiondatetime)
    .Take(1)
    .DefaultIfEmpty()
where lasthistory != null
    && lasthistory.actiondatetime >= periodFrom
    && lasthistory.actiondatetime < periodTo.AddDays(1)
select new
{
    id = doc.id,
    lastactionby = lasthistory.actionby,
    lastactiondatetime = lasthistory.actiondatetime
}

注：EF Core 3.0及以上版本会自动将这种依赖于外部表的SelectMany+Take(1)转换为PostgreSQL的LATERAL连接。

2. 索引优化 #

为documenthistory表的documentid和actiondatetime字段创建复合索引，进一步提升查询效率：

CREATE INDEX idx_documenthistory_documentid_actiondatetime ON dbo.documenthistory (documentid, actiondatetime DESC);

3. 低版本EF兼容写法 #

如果使用EF Core 2.x等较低版本，可以直接编写原生SQL实现预期逻辑：

var query = context.Set<YourDtoType>()
    .FromSqlRaw(@"
        SELECT d.id, t.actionby AS lastactionby, t.actiondatetime AS lastactiondatetime
        FROM dbo.document AS d
        LEFT JOIN LATERAL (
            SELECT d0.actionby, d0.actiondatetime
            FROM dbo.documenthistory AS d0
            WHERE d0.documentid = d.id
            ORDER BY d0.actiondatetime DESC
            FETCH FIRST 1 ROW ONLY
        ) t ON true
        WHERE (t.actiondatetime >= @periodFrom) AND (t.actiondatetime < @periodTo)",
        new NpgsqlParameter("@periodFrom", periodFrom),
        new NpgsqlParameter("@periodTo", periodTo.AddDays(1))
    );

注：需提前定义与查询结果字段匹配的DTO类YourDtoType。

内容的提问来源于stack exchange，提问作者James