ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как еще ускорить case insensitive поиск?
20 сообщений из 20, страница 1 из 1
Как еще ускорить case insensitive поиск?
    #39093274
Фотография Leonid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid
Участник
Тестируем PostgreSQL 9.4 на предмет возможности использования в проекте в качестве альтернативного бесплатного сервера.
Сразу скажу, что Постгрес я вижу всего вторую неделю.
Но для принятия решения собственно углубляться в него или нет нужны хоть какие-то обоснования его приемлемости для нашей задачи кроме аргумента, что тупо бесплатно.

Короче потестировал его на простенькой базе в которой всего ~ 1,5 ляма записей в табличке.
Табличка типа:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE "Phones"
(
  "ph_ID" integer NOT NULL,
  ... // тут разные другие поля не имеющие отношения к делу
  "ph_Name" character varying(50),
  "ph_Name2" citext,
  CONSTRAINT "PTS_PK" PRIMARY KEY ("ph_ID")
)

Поле ph_Name2 (citext) просто дублирует данные из ph_Name эксперимента ради.
Индексы по полям имен имеются. Хотя, судя по планам запросов не похоже, что они имеют какое-либо значение в данном случае.

Ну а далее простой запрос:
Код: plsql
1.
2.
3.
4.
SELECT *
FROM "Phones"
WHERE "ph_Name" LIKE 'Михайлов%'
ORDER BY "ph_Name"

Результат по всем Михайловым и пр. выдан за 1022 ms, что в общем-то терпимо.
Единственное, что несколько смущает - это стабильно одинаковый результат при любых последующих повторениях запроса, что как бы говорит об отсутствии кэширования.

Но! поскольку есть большая необходимость искать без учета регистра, то ищем по нижнему регистру:
Код: plsql
1.
2.
3.
4.
SELECT *
FROM "Phones"
WHERE lower("ph_Name") LIKE 'михайлов%'
ORDER BY "ph_Name"

Результат 2236 ms В два раза хуже

OK Пробуем ILIKE вместо LIKE:
Код: plsql
1.
2.
3.
4.
SELECT *
FROM "Phones"
WHERE "ph_Name" ILIKE 'Михайлов%'
ORDER BY "ph_Name"

Результат 3200 ms В три раза хуже

Последняя попытка поиск по citext полю:
Код: plsql
1.
2.
3.
4.
SELECT *
FROM "Phones"
WHERE "ph_Name2" LIKE 'Михайлов%'
ORDER BY "ph_Name2"

Результат 3310 ms Худший результат


Для сравнения MSSQL на ровно той же машине и такой же базе и с Case Insensative collation ( без учета регистра ) на том же запросе выдает 903ms на "непрогретом" кэше и всего 240ms на "прогретом".

В связи с этим, PostgreSQL-гуру, пожалуйста, расскажите / подскажите, чего бы еще можно подкрутить / повертеть в PostgreSQL, чтобы немного улучшить результаты поиска без учета регистра?
...
Рейтинг: 0 / 0
03.11.2015, 01:39
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093279
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
Leonid,

попробуйте прикрутить индексы. например чёнибудь триграмоное .
...
Рейтинг: 0 / 0
03.11.2015, 02:15
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093301
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Leonid,

сделайте:

Код: sql
1.
create index concurrently on "Phones" using btree(lower(ph_name) text_pattern_ops)



и покажите план (explain (analyze, buffers)) второго запроса. судя по времени выполнения имеющиеся индексы сейчас вообще не используются (или по полтаблицы выбирается).
...
Рейтинг: 0 / 0
03.11.2015, 06:46
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093379
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
LeonidРезультат 2236 ms В два раза хуже полагаю и количество строк поболе.
Если нужна только кириллица, то используй БД с однобайтовой кодировкой.
...
Рейтинг: 0 / 0
03.11.2015, 09:27
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093381
ursido
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ursido
Гость
Leonid...
Но для принятия решения собственно углубляться в него или нет нужны...

Нет нужды. Оставайтесь там, где Вы есть.


Для начала расскажите:
- где стоит PostgreSQL (если на винде - то это ой!)
- какие настройки сервера выполнены (если оставлено по умолчанию - это ой!)
- какие усилия были предприняты для ускорения выполнения тестового запроса? Все ли возможности задействованы?

Магии не существует.
...
Рейтинг: 0 / 0
03.11.2015, 09:31
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093413
Фотография Leonid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid
Участник
AlexiusLeonid,
сделайте:
Код: sql
1.
create index concurrently on "Phones" using btree(lower(ph_name) text_pattern_ops)


О! Спасибо!
До тонкостей типа "Operator Classes" в индексах я без Вас еще не скоро бы добрался.

Т.е. если я правильно понимаю, то в Постгресе всегда нужно штамповать 2 индекса по текстовому полю, если есть желание искать как по равенству так и по LIKE с начала строки. Т.е. в моем примере:
Код: plsql
1.
2.
3.
CREATE INDEX "IX_Phones_Name" ON "Phones" USING btree (lower("ph_Name"));

CREATE INDEX "IX_Phones_Name_ops" ON "Phones" USING btree (lower("ph_Name") varchar_pattern_ops);


Время запроса сократилось на приемлемые 600ms благодаря этому индексу.
Спасибо за дельный совет!
...
Рейтинг: 0 / 0
03.11.2015, 10:10
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093429
Фотография Leonid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid
Участник
ursidoLeonid...
Но для принятия решения собственно углубляться в него или нет нужны...
Нет нужды. Оставайтесь там, где Вы есть.
Знаете сильно чем отличаются русскоязычные профессиональные форумы от интернациональных ( преимущественно англоязычных )?
А вот как раз наличием на русскоязычных форумах таких как у Вас "пустых пожеланий" не по делу ;)

Лично я бы и не менял MSSQL. Там и так все без танцев с бубном зачастую работает быстро и годами без особых вопросов .
Но кризис на дворе и все такое... Клиенты прижиместей становятся и на лицензии их жаба душит.

ursidoДля начала расскажите:
- где стоит PostgreSQL (если на винде - то это ой!)
- какие настройки сервера выполнены (если оставлено по умолчанию - это ой!)
- какие усилия были предприняты для ускорения выполнения тестового запроса? Все ли возможности задействованы?
Магии не существует.
Стоит на Винде и стоять там и будет. Ни на какие Линуксы переписывать сервисы и прочее мы пока не собираемся.
Настройками сервера играли при помощи PgTune. Похоже, что разница пока не большая, но под серьезной нагрузкой будет видно. Но моего вопроса это не слишком касается.
По поводу же вопроса уже четко и без вашей общей пурги объяснил Alexius. За что ему еще раз спасибо!
...
Рейтинг: 0 / 0
03.11.2015, 10:30
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093467
ursido
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ursido
Гость
LeonidПо поводу же вопроса уже четко и без вашей общей пурги объяснил Alexius. За что ему еще раз спасибо!

Вы заблуждаетесь. Предыдущий оратор предложил частный случай какого-то действия без каких-либо объяснений. Вы бездумно собезьянничали, затем сделали странный вывод (то самое "всегда").

Из двух представленных индексов - один лишний. Будет лежать мертвым грузом. Почему - вот здесь и начинается разнообразная пурга, которая Вам ни к чему.

Ну и в продолжение аномалий: почему-то задействован тип citext, а вот триграммные индексы никакого интереса не вызвали.
...
Рейтинг: 0 / 0
03.11.2015, 10:56
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093497
Фотография Leonid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid
Участник
ursidoВы заблуждаетесь. Предыдущий оратор предложил частный случай какого-то действия без каких-либо объяснений. Вы бездумно собезьянничали, затем сделали странный вывод (то самое "всегда").

Из двух представленных индексов - один лишний. Будет лежать мертвым грузом. Почему - вот здесь и начинается разнообразная пурга, которая Вам ни к чему.

Ну и в продолжение аномалий: почему-то задействован тип citext, а вот триграммные индексы никакого интереса не вызвали.
Предыдущий оратор в отличии от Вас предложил очень дельный совет, который работает и дает результат.

Я не уверен по поводу необходимости индекса без varchar_pattern_ops. Может быть он и не нужен. Но я читаю оригинальную документацию, где написано:
http://www.postgresql.org/docs/9.4/static/indexes-opclass.html Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >= comparisons to use an index. Such queries cannot use the xxx_pattern_ops operator classes.
Если у Вас есть что возразить по этому поводу, то рад буду выслушать. Естественно, без пурги про "если на винде - то это ой!" или о том, что где-то нужно оставаться ;)
...
Рейтинг: 0 / 0
03.11.2015, 11:21
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093535
ursido
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ursido
Гость
Leonid,

Да-да! Все именно так. Теперь осталось только рассмотреть случай, при котором будут использоваться операции "<, <=, >, or >=" по текстовому полю при работе конкретного приложения.
...
Рейтинг: 0 / 0
03.11.2015, 11:49
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093548
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
ursidoLeonid,

Да-да! Все именно так. Теперь осталось только рассмотреть случай, при котором будут использоваться операции "<, <=, >, or >=" по текстовому полю при работе конкретного приложения.
легко. выборки по диапазонам.

вообще обычный индекс [хенджобом] вы легко используете как варчар--паттерн--опс (за этот опс, вместо реализации лайка на обычном индексе убивать вообще-то надо. это так, к слову. костыль же явный) -- диапазонной техникой. А вот обратное -- вам недоступно.

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT *
FROM "Phones"
WHERE "ph_Name"  --LIKE 'Михайлов%'
>='Михайлов'
AND "ph_Name" <'Михайло'||chr(ascii('в')) -- тут враньё, нужен следующий символ в сортировке encoding-а
-- но мысль демонстрирует
ORDER BY "ph_Name"
...
Рейтинг: 0 / 0
03.11.2015, 12:00
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093554
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwqursidoLeonid,

Да-да! Все именно так. Теперь осталось только рассмотреть случай, при котором будут использоваться операции "<, <=, >, or >=" по текстовому полю при работе конкретного приложения.
легко. выборки по диапазонам.

вообще обычный индекс [хенджобом] вы легко используете как варчар--паттерн--опс (за этот опс, вместо реализации лайка на обычном индексе убивать вообще-то надо. это так, к слову. костыль же явный) -- диапазонной техникой. А вот обратное -- вам недоступно.

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT *
FROM "Phones"
WHERE "ph_Name"  --LIKE 'Михайлов%'
>='Михайлов'
AND "ph_Name" <'Михайло'||chr(ascii('в')+1) -- тут враньё, нужен следующий символ в сортировке encoding-а
-- но мысль демонстрирует
ORDER BY "ph_Name"



как -то сразу отказалась демонстрироваться.
поправил
...
Рейтинг: 0 / 0
03.11.2015, 12:03
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093591
ursido
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ursido
Гость
qwwq,

Раз. Засчитали.
Теперь нужно определиться, используется ли этот случай в конкретном приложении.

Кстати, какой индекс будет использоваться при

WHERE "ph_Name" SIMILAR TO 'Михайло(в|г)%'

Отличается ли постановка задачи (кроме сортировки) от Вашей исходной?

Я предполагаю, что индекс с text_pattern_ops будет задействован.
...
Рейтинг: 0 / 0
03.11.2015, 12:31
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093611
Фотография Leonid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid
Участник
qwwqлегко. выборки по диапазонам...
Да, спасибо. Правда для случая case insensitive это будет
Код: plsql
1.
2.
3.
4.
5.
SELECT *
FROM "Phones"
WHERE lower("ph_Name") >='михайлов'
AND lower("ph_Name") <'михайло'||chr(ascii('в')+1)
ORDER BY "ph_Name"


И это как раз использует индекс без opc. К слову сказать, даже на ~10-20ms быстрее работает.
Но все же синтаксис менее читаемый, чуть хуже переносимый и более баго-возможный.

Но выходит, что индекс без opc все же нужно держать для возможных сравнений.
...
Рейтинг: 0 / 0
03.11.2015, 12:48
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093632
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Leonid,

я это не к тому, что так делать надо в sql--коде.
а к тому, что вместо "каждому оператору--свой индекс", как повелось, разрабам надо было честно расписать использование обычного индекса и для лайк (similar to) тоже

-- нет никаких алгоритмических проблем, чтобы держать по 2 одинаковые вспомогательные структуры данных , вместо одной. Всем прочим, кроме постгреса, вот как-то без этих опсов всё удаётся. Да и мне на коленке собрать запрос для постгреса, пользующий обычный индекс -- тоже. А планировщику -- повторить мою коленку -- нет. "не шмогла", типа.
...
Рейтинг: 0 / 0
03.11.2015, 12:59
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39093651
Фотография Leonid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid
Участник
qwwq...вместо "каждому оператору--свой индекс", как повелось, разрабам надо было честно расписать использование обычного индекса и для лайк (similar to) тоже
Да, это первый из известных мне серверов БД, в котором приходится так геморроиться с индексами для like. И у MS и в Оракле все как-то проще.
Но да что поделаешь? Главное, что в результате все же можно как-то разрулить созданием специфического индекса.
...
Рейтинг: 0 / 0
03.11.2015, 13:13
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39094366
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
Leonidqwwq...вместо "каждому оператору--свой индекс", как повелось, разрабам надо было честно расписать использование обычного индекса и для лайк (similar to) тоже
Да, это первый из известных мне серверов БД, в котором приходится так геморроиться с индексами для like. И у MS и в Оракле все как-то проще.
Но да что поделаешь? Главное, что в результате все же можно как-то разрулить созданием специфического индекса.
может тебе надо в этом поле колейшен сделать регистронезависимый? тогда никаких танцев с индексами, не надо.
...
Рейтинг: 0 / 0
04.11.2015, 12:45
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39094430
Фотография Leonid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid
Участник
Ivan Durakможет тебе надо в этом поле колейшен сделать регистронезависимый? тогда никаких танцев с индексами, не надо.А ты знаешь как сделать в Постгресе колейшен регистронезависимым? Если знаешь, то расскажи как.
Это у Мелкомягких все просто. Задал, например, Cyrillic_General_CI_AS при создании базы или поля и радуйся.
А если Cyrillic_General_CI_AI то даже "ёжик" и "Ежик" сойдутся.
У Постгреса такого разнообразия не видно. Во всяком случае по умолчанию. Только default, C и POSIX. А еще есть citext колонки.
IMHO Проще уж доп.индекс по lower с классом varchar_pattern_ops построить.
...
Рейтинг: 0 / 0
04.11.2015, 15:07
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39094959
tadmin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tadmin
Участник
Leonid,

http://www.postgresql.org/docs/9.4/static/citext.html
...
Рейтинг: 0 / 0
05.11.2015, 10:18
| Ответить | Цитировать | Написать  
Как еще ускорить case insensitive поиск?
    #39095043
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
LeonidIvan Durakможет тебе надо в этом поле колейшен сделать регистронезависимый? тогда никаких танцев с индексами, не надо.А ты знаешь как сделать в Постгресе колейшен регистронезависимым? Если знаешь, то расскажи как.
Это у Мелкомягких все просто. Задал, например, Cyrillic_General_CI_AS при создании базы или поля и радуйся.
А если Cyrillic_General_CI_AI то даже "ёжик" и "Ежик" сойдутся.
У Постгреса такого разнообразия не видно. Во всяком случае по умолчанию. Только default, C и POSIX. А еще есть citext колонки.
IMHO Проще уж доп.индекс по lower с классом varchar_pattern_ops построить.
ну на линухе в нем полно колейшенов.
...
Рейтинг: 0 / 0
05.11.2015, 11:22
| Ответить | Цитировать | Написать  
20 сообщений из 20, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как еще ускорить case insensitive поиск?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39093413&tid=1997657]:
 0ms
get settings:
 8ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 24ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 64ms
get tp. blocked users:
 2ms
others: 249ms
total:  381ms
созд. / загр.: 381ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]