Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / fias медленно ищет / 25 сообщений из 25, страница 1 из 1
25.03.2015, 15:12
    #38916610
dtulyakov
dtulyakov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
В общем это дебют в настройке продакшен постгреса да и процесс создание базы был не мной сделан
О проблеме - собственно смысл в том, что есть база фиас типа КЛАДРа
Поиск идёт около 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:17
    #38916621
ilike to move it, move it
ilike to move it, move it
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
dtulyakovвот список всех индексов в базесоздай индекс adrobj_idx666.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.03.2015, 15:22
    #38916628
Лопата
Лопата
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
ilike to move it, move itdtulyakovвот список всех индексов в базесоздай индекс adrobj_idx666.;o]
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.03.2015, 15:27
    #38916645
dtulyakov
dtulyakov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
ilike to move it, move itdtulyakovвот список всех индексов в базесоздай индекс adrobj_idx666.
кроме таких " гениальных " идей по существу есть что сказать?

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

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

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

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

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

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

я не спец в фиас, поэтому не могу смоделировать проблему. Есть место, где можно скачать постгресовый дамп, чтобы посмотреть на вашу проблему ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.03.2015, 18:11
    #38916870
vyegorov
vyegorov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
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:30
    #38916876
триграма в сердце
триграма в сердце
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
А занахрена вам дампы и эксплайны?
Очевидно, что обычные индексы по непрефиксному like не работают. А вариантов индексирования поиска по паттерну, помимо этого форума и глобального интернета, есть в документации.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.03.2015, 18:53
    #38916891
Лопата
Лопата
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
триграма в сердцеА занахрена вам дампы и эксплайны?
Очевидно, что обычные индексы по непрефиксному like не работают. А вариантов индексирования поиска по паттерну, помимо этого форума и глобального интернета, есть в документации.там будет видно, какой оно индекс пользует, пользует ли, и большая ли база (если тупо сканит)
и т.п.

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

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

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

я не спец в фиас, поэтому не могу смоделировать проблему. Есть место, где можно скачать постгресовый дамп, чтобы посмотреть на вашу проблему ?
дамп базки 1,7Гб в несжатом виде
сейчас затарю и на гуглдиск попробую выложить
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.03.2015, 07:31
    #38917203
dtulyakov
dtulyakov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
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, 08:21
    #38917226
Лопата
Лопата
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
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:29
    #38917235
dtulyakov
dtulyakov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
Oleg Bartunovdtulyakov,

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


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


Что произойдет в плане использования индекса, если транслитерацию не применять?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.03.2015, 08:14
    #38918371
dtulyakov
dtulyakov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
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, 11:38
    #38918732
grufos
grufos
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
fias медленно ищет
Alexiusgrufosнет поддержки многобайтных кодировок (UTF8, ...), но это можно обойти через транслитерацию.


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

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

ссылка на RTFM
http://www.postgresql.org/docs/9.4/static/pgtrgm.html
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / fias медленно ищет / 25 сообщений из 25, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 444ms
Закрыть
start [/forum/topic.php?fid=53&tablet=1&tid=1998087]:
 0ms
get settings:
 6ms
get forum list:
 13ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 164ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 195ms
total:  444ms
x
x
Закрыть


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