Более быстрый поиск записей, в которых первый символ поля не соответствует [A-Za-z]?

В настоящее время у меня есть следующее:

User (id, fname, lname, deleted_at, guest)

Я могу запросить список пользователей по их инициалам fname следующим образом:

User Load (9.6ms)  SELECT "users".* FROM "users" WHERE (users.deleted_at IS NULL) AND (lower(left(fname, 1)) = 's') ORDER BY fname ASC LIMIT 25 OFFSET 0

Это быстро благодаря следующему индексу:

  CREATE INDEX users_multi_idx
  ON users (lower(left(fname, 1)), fname)
  WHERE deleted_at IS NULL;

Что я хочу сделать сейчас, так это иметь возможность запрашивать всех пользователей, которые не начинаются с буквы A-Z. Я заставил это работать так:

SELECT "users".* FROM "users" WHERE (users.deleted_at IS NULL) AND (lower(left(fname, 1)) ~ E'^[^a-zA-Z].*') ORDER BY fname ASC LIMIT 25 OFFSET 0

Но проблема в том, что этот запрос очень медленный и, похоже, не использует индекс для ускорения первого запроса. Любые предложения о том, как я могу элегантно ускорить второй запрос (не az)?

Я использую Postgres 9.1 с рельсами 3.2.

Спасибо


postgresql ruby-on-rails ruby-on-rails-3 postgresql-performance
person AnApprentice    schedule 16.10.2012    source источник
comment
Спасибо, что показали свои версии. Было бы полезно, если бы вы вставили EXPLAIN ANALYZE обоих запросов в explain.depesz.com и связали их здесь ( вы должны делать это для всех вопросов о производительности), хотя в этом случае достаточно ясно, что 1-й использует индекс, который не может использовать 2-й.   -  person Craig Ringer    schedule 16.10.2012
comment
Пожалуйста, дайте ссылку на предыдущие связанные вопросы, если они существуют, это поможет упростить ответ.   -  person Craig Ringer    schedule 16.10.2012

Ответы (2)


arrow_upward
3
arrow_downward

Обновленный ответ
Предыдущий вопрос здесь.

Моя первая идея (индекс с text_pattern_ops) не сработала. с регулярным выражением в моих тестах. Лучше перепишите свой запрос так:

SELECT *
FROM   users
WHERE  deleted_at IS NULL
WHERE lower(left(fname, 1)) < 'a' COLLATE "C"
OR    lower(left(fname, 1)) > 'z' COLLATE "C"
ORDER  BY fname
LIMIT  25 OFFSET 0;

Помимо того, что эти выражения в целом быстрее, ваше регулярное выражение также содержит заглавные буквы, которые не соответствуют индексу с lower(). И конечные символы были бессмысленны по сравнению с одним символом.

И используйте этот индекс:

CREATE INDEX users_multi_idx
ON users (lower(left(fname, 1)) COLLATE "C", fname)
WHERE deleted_at IS NULL;

Часть COLLATE "C" является необязательной и лишь незначительно повышает производительность. . Его цель состоит в том, чтобы сбросить правила сопоставления к сопоставлению posix по умолчанию, которое просто использует порядок байтов и, как правило, быстрее. Полезно, когда правила сопоставления в любом случае не имеют значения.

Если вы создаете с ним индекс, его могут использовать только запросы, соответствующие параметрам сортировки. Таким образом, вы можете просто пропустить его, чтобы упростить задачу, если производительность не является вашим первостепенным требованием.

person Erwin Brandstetter    schedule 16.10.2012
comment
Спасибо, но это приводит к ошибке: ActionView::Template::Error (PG::Error: ERROR: столбец a не существует LINE 1: ...deleted_at IS NULL) AND (нижний (left (fname, 1)) ‹ ПОДБОРКА... - person AnApprentice; 16.10.2012
comment
@ColdTree: замените двойные кавычки вокруг a одинарными: -› 'a'. - person Erwin Brandstetter; 16.10.2012
comment
Хорошо, что сработало. Я вижу в журнале рельсов запрос User Load (2,2 мс) SELECT users. fname, 1)) › 'z' COLLATE C) ORDER BY fname ASC LIMIT 25 OFFSET 0 ----- Однако прирост производительности достигнут не был. Еще около 1356 мс - person AnApprentice; 16.10.2012
comment
@ColdTree: Извините, у меня больше нет времени. - person Erwin Brandstetter; 16.10.2012
comment
Не беспокойтесь также, заметив, что он не возвращает все ожидаемые результаты, а лишь некоторые из них. - person AnApprentice; 16.10.2012
comment
@ColdTree Всякий раз, когда вы говорите о производительности, публикуйте explain analyze результатов — вы можете вставить их на explain.depesz.com и ссылки на них в комментариях. Если вы не покажете цифры, тогда все будут махать руками в темноте. См. postgresql.org/docs/current/static/sql-explain.html. - person Craig Ringer; 16.10.2012

arrow_upward
2
arrow_downward

В качестве альтернативы общему решению @ErwinBrandstetter PostgreSQL поддерживает частичные индексы. . Ты можешь сказать:

CREATE INDEX users_nonalphanumeric_not_deleted_key
ON users (id)
WHERE (users.deleted_at IS NULL) AND (lower(left(fname, 1)) ~ E'^[^a-zA-Z].*');

Этот индекс не поможет ни для каких других поисков, но он предварительно вычислит ответ для этого конкретного запроса. Этот метод часто полезен для запросов, которые возвращают небольшое предопределенное подмножество из гораздо большей таблицы, поскольку результирующий индекс не будет учитывать большую часть таблицы и будет содержать только интересующие строки.

person willglynn    schedule 16.10.2012
comment
Обратите внимание, что ваш запрос должен будет использовать условие индекса почти дословно; планировщик запросов не очень умен в сопоставлении эквивалентных условий. Например, если вы поменяли местами два предложения вокруг AND, планировщик, вероятно, не будет использовать индекс. Я считаю полезным обернуть условие индекса в функцию SQL, а затем использовать эту функцию SQL везде, где я хочу сослаться на индекс, поскольку это облегчает планировщику их сопоставление. - person Craig Ringer; 16.10.2012