PostgreSQL LATERAL JOIN 不使用 trigram 索引

在本文中，我们将介绍 PostgreSQL 数据库中的 LATERAL JOIN 操作符以及该操作符与 trigram 索引的关系。我们将详细讨论 LATERAL JOIN 的使用方式，并且提供示例来说明不使用 trigram 索引的情况。

阅读更多：PostgreSQL 教程

什么是 LATERAL JOIN

LATERAL JOIN 是 PostgreSQL 中的一个特殊的 JOIN 操作符。它允许在查询中引用 FROM 子句中的前一个表达式的列，从而在表之间建立动态连接关系。这意味着我们可以在关联查询中使用子查询的结果作为参考，并且可以将结果集用于下一个表达式。这种引用的能力称为 “lateral reference”。

LATERAL JOIN 的语法如下所示：

SELECT ... FROM table1, LATERAL (subquery) AS table2 WHERE condition;

在这个语法中，subquery 是一个嵌套的 SELECT 语句，可以引用 table1 中的列。LATERAL 关键字将 subquery 视为表，并将其引入查询的上下文。

LATERAL JOIN 与 trigram 索引的关系

trigram 索引是 PostgreSQL 中的一种特殊索引，用于在文本数据上执行模糊搜索。它将数据分割成三个字符插入索引，从而加快模糊搜索的速度。

LATERAL JOIN 不依赖于 trigram 索引，它是一个基于表达式的 JOIN 操作符，与特定的索引类型无关。因此，可以在使用 LATERAL JOIN 的查询中使用任何适用的索引。

LATERAL JOIN 的示例

考虑以下示例，其中有两个表格：students 和 scores。students 表存储学生的信息，包括学生的 ID 和姓名。scores 表存储学生的分数，包括课程 ID、学生 ID 和分数。

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE scores (
    course_id INTEGER,
    student_id INTEGER,
    score FLOAT
);

INSERT INTO students (name) VALUES
    ('Alice'),
    ('Bob'),
    ('Charlie');

INSERT INTO scores (course_id, student_id, score) VALUES
    (1, 1, 80.0),
    (1, 2, 90.0),
    (2, 2, 85.0),
    (2, 3, 95.0);

现在，我们想要列出每个学生的姓名以及他们的平均分数。我们可以使用 LATERAL JOIN 来实现这个目标：

SELECT s.name, avg_score
FROM students s
LEFT JOIN LATERAL (
    SELECT AVG(score) as avg_score
    FROM scores
    WHERE student_id = s.id
) AS scores_avg ON true;

这个查询中的 LATERAL 子查询引用了外部表 students 的列 s.id，并计算了学生的平均分数。通过在 LATERAL 关键字后指定子查询别名为 scores_avg，我们可以将子查询的结果作为 scores_avg 表引入查询的上下文。最后，通过使用 LEFT JOIN 将学生的姓名和平均分数合并在一起。

总结

LATERAL JOIN 是 PostgreSQL 中的一个有用的特性，它允许在查询中引用前一个表达式的列。它可以通过建立动态连接关系来解决一些复杂的查询需求。LATERAL JOIN 并不依赖于 trigram 索引，它可以与任何适用的索引一起使用。在本文中，我们介绍了 LATERAL JOIN 的基本语法，并且提供了一个示例以说明其使用方式。希望这篇文章对 PostgreSQL 用户理解和使用 LATERAL JOIN 和 trigram 索引有所帮助。

参考链接：
– PostgreSQL Documentation – LATERAL
– PostgreSQL Documentation – Index Types