Совпадение шаблонов с LIKE, ПОДОБНЫМИ или регулярными выражениями в PostgreSQL

Мне пришлось написать простой запрос, где я ищешь имя пользователя, которое начинается с B или D:

SELECT s.name 
FROM spelers s 
WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'
ORDER BY 1

Мне было интересно, есть ли способ переписать это, чтобы стать более результативным. Поэтому я могу избежать or и /или like?

72 голоса | спросил Lucas Kauffman 15 Jpm1000000pmSun, 15 Jan 2012 15:24:02 +040012 2012, 15:24:02

8 ответов


131

Ваш запрос в значительной степени оптимален. Синтаксис не будет намного короче, запрос не будет намного быстрее:

SELECT name
FROM   spelers
WHERE  name LIKE 'B%' OR name LIKE 'D%'
ORDER  BY 1;

Если вы действительно хотите сократить синтаксис , используйте регулярное выражение с ветвями :

...
WHERE  name ~ '^(B|D).*'

Или немного быстрее, с классом символов :

...
WHERE  name ~ '^[BD].*'

Быстрый тест без индекса дает более быстрые результаты, чем для SIMILAR TO в любом случае для меня.
При наличии соответствующего индекса B-Tree LIKE выигрывает эту гонку на порядки.

Прочитайте основы сопоставления образцов в руководстве .

Индекс для превосходной производительности

Если вы заинтересованы в производительности, создайте такой индекс для больших таблиц:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

Делает этот запрос быстрее на порядок. Особые соображения применимы к локальному порядку сортировки. Подробнее о классах операторов в руководстве . Если вы используете стандартную локалью «C» (большинство людей этого не делают), будет использоваться простой индекс (с классом оператора по умолчанию).

Такой индекс хорош только для шаблонов с левой привязкой (совпадающих с началом строки).

SIMILAR TO или регулярные выражения с основными левыми привязанными выражениями также могут использовать этот индекс. Но not с ветвями (B|D) или символьными классами [BD] (по крайней мере, в моих тестах на PostgreSQL 9.0).

Триграммные соответствия или текстовый поиск используют специальные индексы GIN или GiST.

Обзор операторов сопоставления шаблонов

  • LIKE ( ~~ ) прост и быстр, но ограничен в своих возможностях.
    ILIKE ( ~~* ) нечувствительный к регистру вариант.
    pg_trgm расширяет поддержку индексов для обоих.

  • ~ (регулярное выражение) является мощным, но более сложным и может быть медленным для чего-либо большего, чем базовые выражения.

  • ПОДОБНОЕ TO - это просто бессмысленно . Своеобразная полукровка ~ и регулярные выражения. Я никогда не использую его. Пояснение ниже.

  • % оператор «подобия», предоставляемый дополнительным модулем pg_trgm.

  • SIMILAR TO является оператором текстового поиска. См. Ниже.

pg_trgm - сопоставление триграмм

Начиная с PostgreSQL 9.1 вы можете упростить расширение LIKE , чтобы обеспечить поддержку индексов для любого шаблона LIKE /@@ (и простых шаблонов регулярных выражений с ~) с использованием индекса GIN или GiST.

Подробности, пример и ссылки:

pg_trgm также предоставляет оператор «подобия» pg_trgm

Текстовый поиск

Является специальным типом сопоставления шаблонов с отдельной инфраструктурой и типами индексов. Он использует словари и основывается и является отличным инструментом для поиска слов в документах, особенно для естественных языков.

Поддерживается префикс :

Также как поиск фразы , так как Postgres 9.6:

Рассмотрим введение в руководстве и обзор операторов и функций .

Дополнительные инструменты для сопоставления нечеткой строки

Дополнительный модуль fuzzystrmatch предлагает еще несколько вариантов, но производительность, как правило, уступает всем вышеперечисленным.

В частности, различные реализации функции LIKE могут быть полезными.

Почему регулярные выражения (ILIKE) всегда быстрее, чем ~?

Ответ прост. % выражения переписываются внутри регулярных выражений. Таким образом, для каждого выражения levenshtein() существует как минимум одно более быстрое регулярное выражение (что экономит накладные расходы на переписывание выражения). Нет увеличения производительности при использовании ~ .

И простые выражения, которые могут выполняться с помощью SIMILAR TO (SIMILAR TO), быстрее с SIMILAR TO в любом случае.

SIMILAR TO поддерживается только в PostgreSQL, потому что он оказался в ранних версиях стандарта SQL. Они до сих пор не избавились от него. Но есть планы удалить его и включить регулярные выражения вместо - или так я слышал.

LIKE показывает это. Просто попробуйте с самим столом!

~~

показывает:

LIKE

SIMILAR TO был переписан с регулярным выражением (EXPLAIN ANALYZE).

Максимальная производительность для данного случая

Но EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO 'B%'; показывает больше. Попробуйте, с указанным выше индексом:

...  
Seq Scan on spelers  (cost= ...  
  Filter: (name ~ '^(?:B.*)$'::text)

показывает:

SIMILAR TO

Внутренне с индексом, который не является локальным (~ или с использованием locale EXPLAIN ANALYZE), простые лево-привязанные выражения переписываются с этими операторами текстового шаблона: EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ '^B.*; , ... -> Bitmap Heap Scan on spelers (cost= ... Filter: (name ~ '^B.*'::text) -> Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ... Index Cond: ((prod ~>=~ 'B'::text) AND (prod ~<~ 'C'::text)) , text_pattern_ops, C. Это относится к ~>=~, ~<=~ или ~>~.

То же самое верно для индексов в типах ~<~ с ~ или ~~ с помощью SIMILAR TO.

Итак, применительно к исходному вопросу это самый быстрый способ :

varchar

Конечно, если вам нужно найти смежные инициалы , вы можете упростить:

varchar_pattern_ops

Коэффициент усиления по сравнению с обычным использованием char или bpchar_pattern_ops крошечный. Если производительность не является вашим основным требованием, вы должны просто придерживаться стандартных операторов - при достижении того, что у вас уже есть в вопросе.

ответил Erwin Brandstetter 15 Jpm1000000pmSun, 15 Jan 2012 15:38:51 +040012 2012, 15:38:51
10

Как добавить столбец в таблицу. В зависимости от ваших фактических потребностей:

person_name_start_with_B_or_D (Boolean)

person_name_start_with_char CHAR(1)

person_name_start_with VARCHAR(30)

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

В качестве альтернативы, индекс в выражении даст вам то же самое, дешевле. Например:.

CREATE INDEX spelers_name_initial_idx ON spelers (left(name, 1)); 

Запросы, соответствующие выражению в их условиях, могут использовать этот индекс.

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

ответил onedaywhen 16 Jpm1000000pmMon, 16 Jan 2012 12:40:42 +040012 2012, 12:40:42
7

Вы можете попробовать

SELECT s.name
FROM   spelers s
WHERE  s.name SIMILAR TO '(B|D)%' 
ORDER  BY s.name

Я понятия не имею, может ли указанное выше или ваше оригинальное выражение быть размещенным в Postgres.

Если вы создадите предложенный индекс, вам также будет интересно узнать, как это сравнивается с другими параметрами.

SELECT name
FROM   spelers
WHERE  name >= 'B' AND name < 'C'
UNION ALL
SELECT name
FROM   spelers
WHERE  name >= 'D' AND name < 'E'
ORDER  BY name
ответил Martin Smith 15 Jpm1000000pmSun, 15 Jan 2012 15:37:42 +040012 2012, 15:37:42
1

Очень старый вопрос, но я нашел еще одно быстрое решение этой проблемы:

SELECT s.name 
FROM spelers s 
WHERE ascii(s.name) in (ascii('B'),ascii('D'))
ORDER BY 1

Так как функция ascii () смотрит только на первый символ строки.

ответил Sole021 25 62017vEurope/Moscow11bEurope/MoscowSat, 25 Nov 2017 15:55:46 +0300 2017, 15:55:46
0

То, что я делал в прошлом, столкнулось с аналогичной проблемой производительности, заключается в том, чтобы увеличить ASCII-символ последней буквы и сделать МЕЖДУ. Затем вы получаете максимальную производительность для подмножества функций LIKE. Конечно, он работает только в определенных ситуациях, но для сверхбольших наборов данных, где вы, например, ищете имя, это приводит к тому, что производительность переходит от абсурдной к приемлемой.

ответил Mel Padden 19 Jpm1000000pmThu, 19 Jan 2012 22:23:44 +040012 2012, 22:23:44
0

Для проверки инициалов я часто использую casting для "char" (с двойными кавычками). Это не портативный, но очень быстрый. Внутри он просто детонирует текст и возвращает первый символ, а операции сравнения «char» выполняются очень быстро, поскольку тип является 1-байтовой фиксированной длиной:

SELECT s.name 
FROM spelers s 
WHERE s.name::"char" =ANY( ARRAY[ "char" 'B', 'D' ] )
ORDER BY 1

Обратите внимание, что приведение к "char" выполняется быстрее, чем ascii() slution by @ Sole021, но это не совместимо с UTF8 (или любая другая кодировка в этом отношении ), возвращая просто первый байт, поэтому его следует использовать только в тех случаях, когда сравнение проводится против простых старых 7-битных символов ASCII.

ответил Ziggy Crueltyfree Zeitgeister 10 AMpTue, 10 Apr 2018 04:01:16 +030001Tuesday 2018, 04:01:16
0

Существует два метода, не упомянутых еще для рассмотрения таких случаев:

  1. частичный (или секционированный - если создан для полного диапазона вручную) индекс - наиболее полезно, когда требуется только подмножество данных (например, во время некоторого обслуживания или временного для некоторых отчетов):

    CREATE INDEX ON spelers WHERE name LIKE 'B%'
    
  2. разбиение самой таблицы (с использованием первого символа в качестве ключа секционирования) - этот метод особенно стоит учитывать в PostgreSQL 10+ (менее болезненное разбиение) и 11+ (обрезка раздела во время выполнения запроса).

Кроме того, если данные в таблице отсортированы, можно воспользоваться индекс BRIN (над первым символом).

ответил Tomasz Pala 19 Mayam18 2018, 02:16:00
-4

Вероятно, быстрее выполнить одиночное сравнение символов:

SUBSTR(s.name,1,1)='B' OR SUBSTR(s.name,1,1)='D'
ответил user2653985 13 Jpm1000000pmWed, 13 Jan 2016 18:13:27 +030016 2016, 18:13:27

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132