ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / fias медленно ищет
25 сообщений из 25, страница 1 из 1
fias медленно ищет
    #38916610
dtulyakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dtulyakov
Гость
В общем это дебют в настройке продакшен постгреса да и процесс создание базы был не мной сделан
О проблеме - собственно смысл в том, что есть база фиас типа КЛАДРа
Поиск идёт около 5 секунд (долго для ввода поля в веб формочке)
Запрос у нас такого типа:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
                SELECT
                        ao.id
                        ,ao.fullname
                        ,ao.formalname as obj
                        ,cast(aolevel as char)||ao.fullname as xx
                FROM
                        addrobj ao
                INNER JOIN addrobj_links aol1
                        ON
                                ao.id=aol1.addrobj_id
                WHERE
                        livestatus='1'
                        and  actstatus='1'
                        AND formalname ilike '%'||:street||'%'
                ORDER BY  ao.aolevel,formalname limit 10;


вот список всех индексов в базе
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
 schema |          index          
--------+-------------------------
 public | addrobjFullTsvr_pkey
 public | addrobj_idx
 public | addrobj_idx1
 public | addrobj_idx11
 public | addrobj_idx15
 public | addrobj_idx2
 public | addrobj_idx3
 public | addrobj_idx4
 public | addrobj_idx5
 public | addrobj_idx6
 public | addrobj_links_idx1
 public | addrobj_links_idx2
 public | addrobj_links_idx3
 public | addrobj_links_new_pkey
 public | addrobj_links_pkey
 public | addrobj_pkey
 public | direction_projects_pkey
 public | directions_pkey
 public | geo_pkey
 public | movie_pkey
 public | projects_pkey
(21 rows)


Если кто настраивал это счастье то подскажите?, что сделать.
Или не морочить голову с индексами и сразу прикручивать sphinxsearch?
...
Рейтинг: 0 / 0
25.03.2015, 15:12
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38916621
ilike to move it, move it
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ilike to move it, move it
Гость
dtulyakovвот список всех индексов в базесоздай индекс adrobj_idx666.
...
Рейтинг: 0 / 0
25.03.2015, 15:17
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38916628
Лопата
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Лопата
Участник
ilike to move it, move itdtulyakovвот список всех индексов в базесоздай индекс adrobj_idx666.;o]
...
Рейтинг: 0 / 0
25.03.2015, 15:22
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38916645
dtulyakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dtulyakov
Гость
ilike to move it, move itdtulyakovвот список всех индексов в базесоздай индекс adrobj_idx666.
кроме таких " гениальных " идей по существу есть что сказать?

ЗЫ я с фиасом не работал и как оно там устроено понятия не имею, да и постгрес тоже особо не юзал
...
Рейтинг: 0 / 0
25.03.2015, 15:27
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38916714
какбе
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
какбе
Гость
dtulyakov постгрес тоже особо не юзалоно, какбе, заметно

умного учить -- только портить
...
Рейтинг: 0 / 0
25.03.2015, 16:06
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38916733
dtulyakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dtulyakov
Гость
какбеdtulyakov постгрес тоже особо не юзалоно, какбе, заметно

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

ЗЫ если есть желание поумничать то можно форум автомобилистов пойти и там блистать своими скуль познаниями :D
...
Рейтинг: 0 / 0
25.03.2015, 16:18
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38916752
курсы по поиску в интернете
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
курсы по поиску в интернете
Гость
dtulyakovна любом форуме есть те кто знает ответ и те кто только умничает либо отправляет в гугли те, кто не знает ответ и не желает пользоваться гуглом.

dtulyakovесли нет желания помогать или указывать направления в поиске проблемы то лучше не отвечатьесли нет желания научиться, хотя бы искать в гугле по ключевым словам, то не стоит обижаться на тех, кто не хочет это делать за тебя, но вынужден натыкаться на одни и те же вопросы, как завсегдатай форума.
...
Рейтинг: 0 / 0
25.03.2015, 16:32
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38916812
.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
.
Гость
dtulyakov куча ответов и не одного по существу

Один из вариантов точного ответа:
 
КГ/АМ
...
Рейтинг: 0 / 0
25.03.2015, 17:30
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38916869
Oleg Bartunov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg Bartunov
Участник
dtulyakov,

я не спец в фиас, поэтому не могу смоделировать проблему. Есть место, где можно скачать постгресовый дамп, чтобы посмотреть на вашу проблему ?
...
Рейтинг: 0 / 0
25.03.2015, 18:11
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38916870
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
dtulyakov,

запостить выхлоп такого запроса
Код: sql
1.
2.
3.
SELECT name,setting FROM pg_settings WHERE source NOT IN ('default','override')
UNION ALL
SELECT'version'::text,version()::text;



к проблемному запросу в начало дописать `EXPLAIN (analyze, buffers) `, выхлоп скормить в http://explain.depesz.com/ и запостить сюда ссылку на разобранный план и сам запрос.
...
Рейтинг: 0 / 0
25.03.2015, 18:11
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38916876
триграма в сердце
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
триграма в сердце
Гость
А занахрена вам дампы и эксплайны?
Очевидно, что обычные индексы по непрефиксному like не работают. А вариантов индексирования поиска по паттерну, помимо этого форума и глобального интернета, есть в документации.
...
Рейтинг: 0 / 0
25.03.2015, 18:30
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38916891
Лопата
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Лопата
Участник
триграма в сердцеА занахрена вам дампы и эксплайны?
Очевидно, что обычные индексы по непрефиксному like не работают. А вариантов индексирования поиска по паттерну, помимо этого форума и глобального интернета, есть в документации.там будет видно, какой оно индекс пользует, пользует ли, и большая ли база (если тупо сканит)
и т.п.

ну вот так решили расспрашивать. Моно с порога count-ы попросить, и ddl того, что в запросе. Всё равно придётся в каком -то порядке этот черный ящик опрашивать. Само оно не рюхает пока. А если авторы полнотекста решили вписаться сами -- что ж плохого, последим за руками.
мне-то лениво было от печки толкать. вам тоже.
...
Рейтинг: 0 / 0
25.03.2015, 18:53
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38917044
Oleg Bartunov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oleg Bartunov
Участник
триграма в сердцеА занахрена вам дампы и эксплайны?
Очевидно, что обычные индексы по непрефиксному like не работают. А вариантов индексирования поиска по паттерну, помимо этого форума и глобального интернета, есть в документации.

если строки не длинные,то можно подумать про wildspeed
http://www.sai.msu.su/~megera/wiki/wildspeed
...
Рейтинг: 0 / 0
25.03.2015, 22:05
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38917060
/\/\/\/\/\/\
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
/\/\/\/\/\/\
Гость
Oleg Bartunov,

Да-да. Само время для дебюта настройки устанавливать какой-то контриб с необходимостью компиляции.
...
Рейтинг: 0 / 0
25.03.2015, 22:25
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38917200
dtulyakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dtulyakov
Гость
Oleg Bartunovdtulyakov,

я не спец в фиас, поэтому не могу смоделировать проблему. Есть место, где можно скачать постгресовый дамп, чтобы посмотреть на вашу проблему ?
дамп базки 1,7Гб в несжатом виде
сейчас затарю и на гуглдиск попробую выложить
...
Рейтинг: 0 / 0
26.03.2015, 07:26
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38917203
dtulyakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dtulyakov
Гость
vyegorovdtulyakov,

запостить выхлоп такого запроса
Код: sql
1.
2.
3.
SELECT name,setting FROM pg_settings WHERE source NOT IN ('default','override')
UNION ALL
SELECT'version'::text,version()::text;



к проблемному запросу в начало дописать `EXPLAIN (analyze, buffers) `, выхлоп скормить в http://explain.depesz.com/ и запостить сюда ссылку на разобранный план и сам запрос.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
            name            |                                                setting                                                
----------------------------+-------------------------------------------------------------------------------------------------------
 application_name           | psql
 client_encoding            | UTF8
 DateStyle                  | ISO, MDY
 default_text_search_config | pg_catalog.english
 dynamic_shared_memory_type | posix
 external_pid_file          | /var/run/postgresql/9.4-main.pid
 lc_messages                | C
 lc_monetary                | C
 lc_numeric                 | C
 lc_time                    | C
 listen_addresses           | *
 log_line_prefix            | %t [%p-%l] %q%u@%d 
 log_timezone               | Europe/Volgograd
 max_connections            | 100
 max_stack_depth            | 2048
 port                       | 5432
 shared_buffers             | 16384
 ssl_prefer_server_ciphers  | off
 stats_temp_directory       | /var/run/postgresql/9.4-main.pg_stat_tmp
 TimeZone                   | Europe/Volgograd
 unix_socket_directories    | /var/run/postgresql
 version                    | PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
(22 rows)


по поводу запроса вот разобранный план
и сам запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
SELECT
    ao.id
    ,ao.fullname
    ,ao.formalname as obj
    ,cast(aolevel as char)||ao.fullname as xx
FROM
    addrobj ao
INNER JOIN addrobj_links aol1
ON
    ao.id=aol1.addrobj_id
WHERE
    livestatus='1'
    and  actstatus='1'
    AND formalname ilike '%содовая%'
    ORDER BY  ao.aolevel,formalname limit 10;
   id    |                 fullname                  |     obj     |                     xx                     
---------+-------------------------------------------+-------------+--------------------------------------------
  278978 | край Пермский..г Березники.ул Новосодовая | Новосодовая | 7край Пермский..г Березники.ул Новосодовая
  278978 | край Пермский..г Березники.ул Новосодовая | Новосодовая | 7край Пермский..г Березники.ул Новосодовая
 2523164 | край Пермский..г Березники. ул Содовая    | Содовая     | 7край Пермский..г Березники. ул Содовая
 2523164 | край Пермский..г Березники. ул Содовая    | Содовая     | 7край Пермский..г Березники. ул Содовая
(4 rows)
...
Рейтинг: 0 / 0
26.03.2015, 07:31
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38917226
Лопата
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Лопата
Участник
dtulyakov,
у вас 4-й шаг
Код: sql
1.
2.
3.
4.
Seq Scan on addrobj ao  (cost=0.00..314263.76 rows=37 width=127) (actual time=2450.473..4784.596 rows=2 loops=1)
                     Filter: (((formalname)::text ~~* '%содовая%'::text) AND (livestatus = '1'::text) AND (actstatus = '1'::text))
                     Rows Removed by Filter: 2481813
                     Buffers: shared hit=15007 read=255825


--Full scan таблички. . так оно не заиграет.



приведите все ddl ваших индексов. Хотя бы тех, которые имеют в ddl чт-то помимо btree

и вот что вот это вот " addrobjFullTsvr_pkey" такое -- тложе приведите.
...
Рейтинг: 0 / 0
26.03.2015, 08:21
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38917235
dtulyakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dtulyakov
Гость
Oleg Bartunovdtulyakov,

я не спец в фиас, поэтому не могу смоделировать проблему. Есть место, где можно скачать постгресовый дамп, чтобы посмотреть на вашу проблему ?
fias.sql.txz
...
Рейтинг: 0 / 0
26.03.2015, 08:29
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38917997
Фотография grufos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
grufos
Участник
dtulyakov,
для убыстрения подобных операций можно использовать работу с триграммами
dtulyakov
Код: sql
1.
AND formalname ilike '%содовая%'


особенности:
нет поддержки многобайтных кодировок (UTF8, ...), но это можно обойти через транслитерацию.
Если у вас БД в однобайтной кодировке (CP1251, ...) то тогда и проблем нет

1. добавляем расширение для работы с триграммами
CREATE EXTENSION pg_trgm;
2. создаем индекс на нужном поле
CREATE INDEX addrobj_formalname_idx ON addrobj USING gist (formalname gist_trgm_ops);
3. теперь ищем
select * from addrobj where formalname ~ 'содовая';

если же у вас многобайтная кодировка, то
2. создаем функциональный индекс
CREATE INDEX addrobj_formalname_idx ON addrobj USING gist (russian2translit(formalname) gist_trgm_ops);
где, russian2translit функция трансформации русского текста в транслит.
3. теперь ищем
select * from addrobj where russian2translit(formalname) ~ russian2translit('содовая');

после этого тормоза связанные с поиском по паттерну вас покинут
...
Рейтинг: 0 / 0
26.03.2015, 17:43
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38918019
улица без города
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
улица без города
Гость
grufosпосле этого тормоза связанные с поиском по паттерну вас покинут или приумножатся для экстремально коротких паттернов.
...
Рейтинг: 0 / 0
26.03.2015, 18:05
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38918059
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
grufosнет поддержки многобайтных кодировок (UTF8, ...), но это можно обойти через транслитерацию.


эммм, как это нет?
...
Рейтинг: 0 / 0
26.03.2015, 18:47
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38918365
/\/\/\/\/\/\
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
/\/\/\/\/\/\
Гость
grufosнет поддержки многобайтных кодировок (UTF8, ...), но это можно обойти через транслитерацию.


Что произойдет в плане использования индекса, если транслитерацию не применять?
...
Рейтинг: 0 / 0
27.03.2015, 07:54
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38918371
dtulyakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dtulyakov
Гость
grufosdtulyakov,
для убыстрения подобных операций можно использовать работу с триграммами
dtulyakov
Код: sql
1.
AND formalname ilike '%содовая%'


особенности:
нет поддержки многобайтных кодировок (UTF8, ...), но это можно обойти через транслитерацию.
Если у вас БД в однобайтной кодировке (CP1251, ...) то тогда и проблем нет

1. добавляем расширение для работы с триграммами
CREATE EXTENSION pg_trgm;
2. создаем индекс на нужном поле
CREATE INDEX addrobj_formalname_idx ON addrobj USING gist (formalname gist_trgm_ops);
3. теперь ищем
select * from addrobj where formalname ~ 'содовая';

если же у вас многобайтная кодировка, то
2. создаем функциональный индекс
CREATE INDEX addrobj_formalname_idx ON addrobj USING gist (russian2translit(formalname) gist_trgm_ops);
где, russian2translit функция трансформации русского текста в транслит.
3. теперь ищем
select * from addrobj where russian2translit(formalname) ~ russian2translit('содовая');

после этого тормоза связанные с поиском по паттерну вас покинут
Большое спасибо поиск занимает около секунды
ищет в UTF-8 кодировке нормально без транслита
ЗЫ надо будет покурить работу с триграммами да и сам постгрес в целом
...
Рейтинг: 0 / 0
27.03.2015, 08:14
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38918732
Фотография grufos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
grufos
Участник
Alexiusgrufosнет поддержки многобайтных кодировок (UTF8, ...), но это можно обойти через транслитерацию.


эммм, как это нет?

Да, с UTF8 поиском всё в порядке.
Прощу прощения за ввод в заблуждение
...
Рейтинг: 0 / 0
27.03.2015, 11:38
| Ответить | Цитировать | Написать  
fias медленно ищет
    #38918754
Фотография grufos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
grufos
Участник
dtulyakovЗЫ надо будет покурить работу с триграммами да и сам постгрес в целом

ссылка на RTFM
http://www.postgresql.org/docs/9.4/static/pgtrgm.html
...
Рейтинг: 0 / 0
27.03.2015, 11:44
| Ответить | Цитировать | Написать  
25 сообщений из 25, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / fias медленно ищет
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=38918365&tid=1998087]:
 0ms
get settings:
 11ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 178ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 87ms
get tp. blocked users:
 2ms
others: 234ms
total:  546ms
созд. / загр.: 546ms
x
x
Закрыть


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