performance - PostgreSQL:在所有表字段的长度上创建索引

Question

我有一个名为 profile 的表格，我想根据哪些表格填写得最多来对它们进行排序。每个列都是 JSONB 列或 TEXT 列。我不需要在很大程度上确定这一点，所以通常我会按如下方式订购:

SELECT * FROM profile ORDER BY LENGTH(CONCAT(profile.*)) DESC;

但是，这很慢，所以我想创建一个索引。但是，这不起作用:

CREATE INDEX index_name ON profile (LENGTH(CONCAT(*))

也没有

CREATE INDEX index_name ON profile (LENGTH(CONCAT(CAST(* AS TEXT))))

不能说我很惊讶。声明此索引的正确方法是什么？

Answer 1

要测量文本表示中行的大小，您可以将整行转换为文本，这比连接单个列快得多:

SELECT length(profile::text) FROM profile;

但是索引中的这个表达式有 3(或 4)个问题:

1. CREATE INDEX不接受简写语法profile::text，您需要添加额外的括号或默认为标准语法cast (配置文件 AS 文本)

2. 还是和@jjanes already discussed 一样的问题: 索引表达式中只允许使用 IMMUTABLE 函数，将行类型转换为 text 不符合此要求。您可以构建一个假的 IMMUTABLE 包装器函数，就像 Jeff 概述的那样。

3. 存在固有的歧义(这也适用于 Jeff 的回答!):如果您的列名与表名相同(这是常见情况)，您不能在 CREATE INDEX 中引用行类型，因为标识符总是先解析为列名。

4. 与原始版本的细微差别:这会向 text 表示形式添加列分隔符、行修饰符和可能的转义字符。对您的用例应该无关紧要。

但是，我建议使用一个更激进的替代方案作为行大小的粗略指标:pg_column_size() .更短、更快，并避免了 1、3 和 4 问题:

SELECT pg_column_size(profile) FROM profile;

问题 2 仍然存在，但:pg_column_size() 也只是 STABLE。您可以创建一个简单且廉价的 SQL 包装器函数:

CREATE OR REPLACE FUNCTION pg_column_size(profile)
  RETURNS int LANGUAGE sql IMMUTABLE AS
'SELECT pg_catalog.pg_column_size($1)';

然后像@jjanes 概述的那样继续。更多详情:

• Does PostgreSQL support "accent insensitive" collations?

请注意，我使用行类型 profile 作为参数创建了函数。 Postgres 允许函数重载，这就是为什么我们可以使用相同的函数名。现在，当我们将匹配的行类型提供给 pg_column_size() 时，我们的自定义函数会根据 function type resolution 进行更紧密的匹配。 规则被选中而不是多态系统函数。或者，使用一个单独的名称并可能使函数多态...

相关:

• Is there a way to disable function overloading in Postgres