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. Синтаксическая стенография profile::text не принимается в CREATE INDEX, вам нужно добавить дополнительные скобки или по умолчанию к стандартному синтаксису cast(profile AS text)

2. Все та же проблема, которую @jjanes уже обсуждал: только IMMUTABLE функции разрешены в выражениях индекса и приведении типа строки к text не проходит это требование. Вы могли бы построить подделку IMMUTABLE функция обертки, как обрисовал в общих чертах Джефф.

3. Существует внутренняя неоднозначность (которая относится и к ответу Джеффа!): Если у вас есть имя столбца, совпадающее с именем таблицы (что является распространенным случаем), вы не можете ссылаться на тип строки в 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. Больше деталей:

• Поддерживает ли PostgreSQL сортировку без учета акцента?

Обратите внимание, что я создал функцию с типом строки profile в качестве параметра. Postgres допускает перегрузку функций, поэтому мы можем использовать одно и то же имя функции. Теперь, когда мы передаем соответствующий тип строки pg_column_size() наша пользовательская функция более точно соответствует правилам разрешения типов функций и выбирается вместо полиморфной системной функции. В качестве альтернативы, используйте отдельное имя и, возможно, сделайте функцию полиморфной...

Связанные с:

• Есть ли способ отключить перегрузку функций в Postgres

Answer 2

Вы можете объявить функцию, которая ошибочно помечена как "неизменяемая", и построить индекс для этого.

CREATE OR REPLACE FUNCTION len_immut(record)
 RETURNS int
 LANGUAGE plperl
 IMMUTABLE
AS $function$
  ## This function lies about its immutability.
  ## Use it with care.  It is useful for indexing
  ## entire table rows.
  return length(join ",", values %{$_[0]});
$function$

а потом

create index on profile (len_immut(profile));

SELECT * FROM profile ORDER BY len_immut(profile) DESC;

Поскольку функция ошибочно помечена как immutableиндекс может устареть, если вы сделаете такие вещи, как добавление или удаление столбцов в таблице или изменение типов столбцов.