如何在 PostgreSQL 中高效存储和访问常用的子查询？

优化 PostgreSQL 中的频繁子查询

PostgreSQL 用户经常遇到依赖重复子查询的查询，通常会聚合相关表中的数据。 本文探讨了管理这些常见子查询模式的有效方法，从而提高性能和代码可读性。 一个典型的场景是根据外键关系从辅助表中检索聚合数据。

视图方法：一个强大的解决方案

虽然直接将子查询嵌入为伪列是不可能的，但 PostgreSQL 的 VIEW 功能提供了一个很好的替代方案。 VIEW 定义了一个基于现有表和查询的虚拟表，有效地创建了数据的自定义视图。这样可以避免修改基表结构，同时实现所需的结果。

生成列（PostgreSQL 11）：增强性能

PostgreSQL 11 及更高版本提供生成列（也称为虚拟列）。这些允许定义一个列，其值是使用表达式计算的，反映了嵌入子查询的目标。 这种方法通常会产生比视图更好的性能。

模拟旧版本中生成的列

对于 PostgreSQL 11 之前的版本，或者首选虚拟列方法时，用户定义的函数可以模拟生成的列。 该函数将表的行类型作为输入并计算所需的值。 然后该函数链接到伪列，实现与生成列类似的功能。

实际实施

我们举个例子来说明一下：

<code class="language-sql">-- Create tables
CREATE TABLE tbl_a (a_id int, col1 int, col2 int);
CREATE TABLE tbl_b (b_id int, a_id int, colx int);

-- Sample data
INSERT INTO tbl_a VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);
INSERT INTO tbl_b VALUES
(1,1,5),  (2,1,5),  (3,1,1)
, (4,2,8),  (5,2,8),  (6,2,6)
, (7,3,11), (8,3,11), (9,3,11);

-- Function to emulate the generated column
CREATE FUNCTION col3(tbl_a)
RETURNS int8
LANGUAGE sql STABLE AS
$func$
SELECT sum(colx)
FROM   tbl_b b
WHERE  b.a_id = .a_id
$func$;</code>

现在，访问col3伪列：

<code class="language-sql">SELECT a_id, col1, col2, tbl_a.col3
FROM   tbl_a;

SELECT *, a.col3 FROM tbl_a a;</code>

生成列的优点

与视图相比，生成的列提供：

• 无缝集成：包含在SELECT *查询中，但没有明确提及。
• 动态更新：自动反映基表中的更改。
• 优化性能：利用 PostgreSQL 的查询优化器提高效率。

结论

虽然不支持直接嵌入子查询，但 PostgreSQL 提供了有效的替代方案——视图和生成列——来管理常用的子查询。 这些方法提高了查询性能和代码清晰度，从而实现更高效的数据库管理。 选择最适合您的 PostgreSQL 版本和性能要求的方法。

以上是如何在 PostgreSQL 中高效存储和访问常用的子查询？的详细内容。更多信息请关注PHP中文网其他相关文章！