Ускорение работы с таблицей в 600000 строк

@Androbim · Регистрация: 04.09.2014

Студворк — интернет-сервис помощи студентам

Доброго времени суток!

На фронтенде реализован функционал автодополнения поля адреса при изменении пользователем строки, ну, это как во всех поисковиках, пользователь добавляет символ - раскрывается список с вариантами.

Соответственно - каждый раз, при добавлении/удалении символа запускается метод бэкенда, который осуществляет поиск в БД (like по части строки) и возвращает массив вариантов.

В таблице содержится примерно 600 000 строк. То есть отработка метода не успевает за нажатием клавиш.
Понятное дело, для пользователей это выглядит как зависание и т п.
Поле с адресом проиндексировано, но проблемы это не решает.

Никто не подскажет, можно ли решить проблему на стороне БД? Я читал, что PostgreSQL умеет как-то выносить таблицы в оперативную память, ну, это как пример. В какую сторону посоветуете посмотреть?

На любые уточняющие вопросы готов ответить.

С уважением.

@Swa111 · 29.11.2024, 10:40

Пришлите DDL для таблицы и индексов а так же запрос + план запроса (explain analyze) для какого нибудь значения. В идеале образцы строк из таблицы
PS. индексы не помогут для Like из середины строки

@grgdvo · 29.11.2024, 23:48

Если помучаться с pg_trgm, то можно и LIKE в середину.

Но предлагаю для начала еще LIMIT 10, например, поставить, чтобы предлагало не более 10 каких-то вариантов.
Не уверен, что пользователю нужны все 600000 вариантов.
Даже 100 - это уже невыносимо.
А из 10 еще можно заставить себя выбрать))

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

Это частично разгрузит проблему.

@Androbim · 02.12.2024, 08:54 **[ТС]**

Сообщение от Swa111

Пришлите DDL для таблицы и индексов а так же запрос + план запроса (explain analyze) для какого нибудь значения. В идеале образцы строк из таблицы
PS. индексы не помогут для Like из середины строки

Рад бы, но не смогу.

@Swa111 · 02.12.2024, 09:03

Помогать по описанию фотографии это уже надо к экстрасенсам наверно.

Как вариант решения это весь поиск сводит к префиксному сравнению т.е. если и Like то только like 'ЧастьАдреса%'.
Индексы для такого поиска должны быть тоже не обычные

SQL
1
2
CREATE INDEX postal_segments_text_x 
  ON postal_segments (postal_code text_pattern_ops);

Для этого возможно придется разбивать адрес на части и поиск искать по иерархии. Плюс эту самую иерархию создавать при добавлении адреса в БД.

Еще посмотрите в сторону поиска по триграммам

@Androbim · 02.12.2024, 09:42 **[ТС]**

Большое спасибо!
Я пока не смотрю на иерархические дела, потому что, сами понимаете, какие доработки это повлечет за собой, тем более, с учетом интеграций.
Задействование nosql - ных решений тоже, на мой взгляд, пользы не принесет.
Есть мысль посмотреть на возможности Postgre в части выведения каких-то сегментов в оперативу, я пока лишь только слышал об этом...

@Swa111 · 02.12.2024, 09:47

Сообщение от Androbim

Я пока не смотрю на иерархические дела, потому что, сами понимаете, какие доработки это повлечет за собой

С точки зрения фронта изменений не много, просто теперь будет дергаться не запрос а функция которая будет возвращать таблицу с данными.

Сообщение от Androbim

Задействование nosql - ных решений тоже, на мой взгляд, пользы не принесет.

триграммы это все в рамках SQL

@Androbim · 02.12.2024, 09:50 **[ТС]**

Сообщение от grgdvo

Но предлагаю для начала еще LIMIT 10, например, поставить, чтобы предлагало не более 10 каких-то вариантов.
Не уверен, что пользователю нужны все 600000 вариантов.
Даже 100 - это уже невыносимо.
А из 10 еще можно заставить себя выбрать))

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

Это частично разгрузит проблему.

Большое спасибо!
Во-первых, извините, забыл сразу сказать - метод начинает отрабатывать с четвертого введенного символа символа.
Допустим, из предложенного лимита юзер ничего не выбрал. Следующий запрос должен как-то учитывать этот момент?
А насчет таймаута идея интересная, как паллиативное решение. Если в это время будут крутиться песочные часы, это создаст иллюзию поиска, и визуально устранит проблему зависания. Спасибо, принято.

@grgdvo · 02.12.2024, 22:08

Сообщение от Androbim

Допустим, из предложенного лимита юзер ничего не выбрал. Следующий запрос должен как-то учитывать этот момент?

Сложно сказать... Очередной введенный символ может кардинально поменять результаты запроса.
Я бы пробовал пока простые варианты, добиться сносного времени, потому уже наводить оптимизацию.

@Аватар · 02.12.2024, 22:39

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

@SecretSilent · 06.12.2024, 10:55

Заменить like на ilike, делов

@Аватар · 06.12.2024, 11:22

Сообщение от SecretSilent

делов

и забыть об индексе. хотя есть еще расширение pg_trgm и триграммные индексы и ilike пользуется ими

@Swa111 · 06.12.2024, 11:30

Сообщение от SecretSilent

Заменить like на ilike, делов

Сообщение от Аватар

и забыть об индексе.

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

SQL
1
2
3
4
CREATE INDEX postal_segments_text_x 
  ON postal_segments (UPPER(postal_code) text_pattern_ops);
 
SELECT * FROM postal_segments_text_x  WHERE UPPER(postal_code) LIKE UPPER('Адре%');

@Androbim · 18.12.2024, 15:42 **[ТС]**

Большое спасибо всем!
А решение вроде in-memory, оно не из этой оперы? Стоит посмотреть в эту сторону?

@Swa111 · 18.12.2024, 15:44

Сообщение от Androbim

Большое спасибо всем!
А решение вроде in-memory, оно не из этой оперы? Стоит посмотреть в эту сторону?

Я бы смотрел в сторону оптимизации алгоритма а не подбор ресурсов под не оптимальный алгоритм

@Androbim · 04.02.2025, 19:55 **[ТС]**

Огромное всем спасибо!
Извините, что отвечаю с таким опозданием!

Причина замедления нашлась. Регулярка в запросе. Стоит ее убрать, и запрос выполняется, самое большее, за 1 секунду. Но проблемы это не решило с "накладками".
В итоге, на фронтенде, вместо раскрывающихся списков, реализовал выборку всех результатов поиска в модальное окно - его пользователь может не спеша прокрутить, выбирая нужную запись. Запрос, соответственно, выполняется один раз.
Пока вот так.

Новые блоги и статьи Все статьи Все блоги /
Семь CDC на одном интерфейсе: 5 U[S]ARTов, 1 CAN и 1 SSI Eddy_Em 18.02.2026 Постепенно допиливаю свою "многоинтерфейсную плату". Выглядит вот так: https:/ / www. cyberforum. ru/ blog_attachment. php?attachmentid=11617&stc=1&d=1771445347 Основана на STM32F303RBT6. На борту пять. . .	Символьное дифференцирование igorrr37 13.02.2026 / * Программа принимает математическое выражение в виде строки и выдаёт его производную в виде строки и вычисляет значение производной при заданном х Логарифм записывается как: (x-2)log(x^2+2) -. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .	И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.
«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»	SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL3_image 8Observer8 10.02.2026 Содержание блога Библиотека SDL3_image содержит инструменты для расширенной работы с изображениями. Пошагово создадим проект для загрузки изображения формата PNG с альфа-каналом (с прозрачным. . .

@Androbim 21 / 21 / 2 Регистрация: 04.09.2014 Сообщений: 155

	Ускорение работы с таблицей в 600000 строк 29.11.2024, 09:17. Показов 1955. Ответов 15 Метки postgesql (Все метки) Доброго времени суток! На фронтенде реализован функционал автодополнения поля адреса при изменении пользователем строки, ну, это как во всех поисковиках, пользователь добавляет символ - раскрывается список с вариантами. Соответственно - каждый раз, при добавлении/удалении символа запускается метод бэкенда, который осуществляет поиск в БД (like по части строки) и возвращает массив вариантов. В таблице содержится примерно 600 000 строк. То есть отработка метода не успевает за нажатием клавиш. Понятное дело, для пользователей это выглядит как зависание и т п. Поле с адресом проиндексировано, но проблемы это не решает. Никто не подскажет, можно ли решить проблему на стороне БД? Я читал, что PostgreSQL умеет как-то выносить таблицы в оперативную память, ну, это как пример. В какую сторону посоветуете посмотреть? На любые уточняющие вопросы готов ответить. С уважением. 0

@Swa111 912 / 286 / 57 Регистрация: 01.06.2023 Сообщений: 810
	29.11.2024, 10:40
	Пришлите DDL для таблицы и индексов а так же запрос + план запроса (explain analyze) для какого нибудь значения. В идеале образцы строк из таблицы PS. индексы не помогут для Like из середины строки 1

@grgdvo 1264 / 978 / 384 Регистрация: 02.09.2012 Сообщений: 3,021
	29.11.2024, 23:48
	Если помучаться с pg_trgm, то можно и LIKE в середину. Но предлагаю для начала еще LIMIT 10, например, поставить, чтобы предлагало не более 10 каких-то вариантов. Не уверен, что пользователю нужны все 600000 вариантов. Даже 100 - это уже невыносимо. А из 10 еще можно заставить себя выбрать)) Включать поиск, если ввели хотя бы три символа (это уже на клиенте регулируется). Зачем гонять запрос на один-два введенных символа, и так понятно, что вернем очень много записей, зачастую совсем не нужных. Врубить таймаут 200-500мс, чтобы не сразу бросаться и обрабатывать каждый символ. Пусть пользователь что-нибудь осмысленное введет... Потом уже бросаться и искать для него что-то. Это частично разгрузит проблему. 3

@Androbim 21 / 21 / 2 Регистрация: 04.09.2014 Сообщений: 155
	02.12.2024, 09:42 [ТС]
	Большое спасибо! Я пока не смотрю на иерархические дела, потому что, сами понимаете, какие доработки это повлечет за собой, тем более, с учетом интеграций. Задействование nosql - ных решений тоже, на мой взгляд, пользы не принесет. Есть мысль посмотреть на возможности Postgre в части выведения каких-то сегментов в оперативу, я пока лишь только слышал об этом... 0

@Аватар 5393 / 1465 / 513 Регистрация: 31.05.2012 Сообщений: 5,153
	02.12.2024, 22:39
	и поиск регистрозависимый же. и для иванов запись с Иванов в базе не найдется 0

@SecretSilent 81 / 81 / 9 Регистрация: 16.02.2010 Сообщений: 637
	06.12.2024, 10:55
	Заменить like на ilike, делов 1

@Androbim 21 / 21 / 2 Регистрация: 04.09.2014 Сообщений: 155
	18.12.2024, 15:42 [ТС]
	Большое спасибо всем! А решение вроде in-memory, оно не из этой оперы? Стоит посмотреть в эту сторону? 0

@Androbim 21 / 21 / 2 Регистрация: 04.09.2014 Сообщений: 155
	04.02.2025, 19:55 [ТС]
	Огромное всем спасибо! Извините, что отвечаю с таким опозданием! Причина замедления нашлась. Регулярка в запросе. Стоит ее убрать, и запрос выполняется, самое большее, за 1 секунду. Но проблемы это не решило с "накладками". В итоге, на фронтенде, вместо раскрывающихся списков, реализовал выборку всех результатов поиска в модальное окно - его пользователь может не спеша прокрутить, выбирая нужную запись. Запрос, соответственно, выполняется один раз. Пока вот так. 0