PostgreSQL：在所有表字段的长度上创建索引

小编典典

javascript sql

我有一个名为的表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))))

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

阅读 548

2020-09-09

共1个答案

小编典典

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

SELECT length(profile::text) FROM profile;

但是索引中的此表达式存在3（或4）个问题：

profile::text不接受语法速记CREATE INDEX，您需要在标准语法中添加额外的括号或默认值cast(profile AS text)
@jjanes已经讨论了同样的问题：IMMUTABLE索引表达式中仅允许使用函数，而将行类型强制转换为text不满足此要求。您可以IMMUTABLE像Jeff概述的那样构建伪造的包装函数。
存在一个固有的歧义（也适用于Jeff的答案！）：如果列名与表名相同（这是一种常见情况），则您不能引用行类型，CREATE INDEX因为标识符始终解析为列名优先。
与原始版本的微小差异：这会在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)';

2020-09-09