PostgreSQL: создать индекс по длине всех полей таблицы

У меня есть таблица с именем 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))))

Не могу сказать, что удивлен. Как правильно объявить этот индекс?


postgresql performance database-performance database-indexes
person jdotjdot    schedule 07.12.2015    source источник
comment
не работает - плохое описание проблемы.   -  person jjanes    schedule 08.12.2015

Ответы (2)


arrow_upward
3
arrow_downward

Чтобы измерить размер строки в текстовом представлении, вы можете просто преобразовать всю строку в текст, что намного быстрее, чем объединение отдельных столбцов:

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. Незначительное отличие от оригинала: добавляются разделители столбцов, декораторы строк и, возможно, escape-символы в представление 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. Подробнее:

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

Связанный:

person Erwin Brandstetter    schedule 07.12.2015

arrow_upward
1
arrow_downward

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

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, индекс может устареть, если вы сделаете что-то вроде добавления или удаления столбцов в таблице или изменения типов столбцов.

person jjanes    schedule 07.12.2015