ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
20 сообщений из 45, страница 2 из 2
  2  все
Оптимизация запроса
    #39172030
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Sergei.AgalakovПопробуйте разбить индекс cause_idx_cause_db_id_in_date на два индекса - по дате и по ID."вы не поняли".

селективность cause_db_id -- с гулькин чих.
это ид "базы данных".



Sergei.AgalakovС %asdf% вам обычные индексы не помогут. Смотрите текстовые индексы в Postgres."вы не поняли"

автор уже понаделал необычных триграммов. ан -- оно не подхватывается.
печаль.
[ и не должны были, из общих соображений о межтабличной структуре его OR фильтра.]
...
Рейтинг: 0 / 0
16.02.2016, 10:38
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172118
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
vyegorov,

EXPLAIN (analyze, buffers):
Код: 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.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
QUERY PLAN
GroupAggregate  (cost=5488.63..5488.67 rows=1 width=312) (actual time=45844.031..45850.971 rows=1415 loops=1)
  Buffers: shared hit=19555 read=6266
  I/O Timings: read=45578.315
  ->  Sort  (cost=5488.63..5488.64 rows=1 width=312) (actual time=45844.008..45844.429 rows=2873 loops=1)
        Sort Key: _c.cause_id, _c.number, _d.judge, _d.judges, _c.in_date, _c.is_active
        Sort Method: quicksort  Memory: 947kB
        Buffers: shared hit=19555 read=6266
        I/O Timings: read=45578.315
        ->  Nested Loop  (cost=0.01..5488.62 rows=1 width=312) (actual time=57.880..45820.944 rows=2873 loops=1)
              Buffers: shared hit=19544 read=6266
              I/O Timings: read=45578.315
              ->  Nested Loop  (cost=0.01..5483.67 rows=1 width=268) (actual time=57.842..45720.910 rows=2879 loops=1)
                    Join Filter: (_c.cause_id = _p.cause_id)
                    Buffers: shared hit=13786 read=6266
                    I/O Timings: read=45578.315
                    ->  Nested Loop  (cost=0.01..5327.96 rows=1 width=161) (actual time=40.032..24485.573 rows=1417 loops=1)
                          Buffers: shared hit=8305 read=3293
                          I/O Timings: read=24402.850
                          ->  Index Scan using cause_idx_cause_db_id_in_date on cause _c  (cost=0.01..4769.78 rows=6 width=33) (actual time=0.158..6248.657 rows=1582 loops=1)
                                Index Cond: ((cause_db_id = 2605) AND (in_date >= to_date('01.03.2015'::text, 'dd.mm.yyyy'::text)) AND (in_date <= to_date('31.03.2015'::text, 'dd.mm.yyyy'::text)))
                                Filter: ((deleted <> 1) AND (COALESCE(include_stat, 1::smallint) = 1))
                                Buffers: shared hit=198 read=962
                                I/O Timings: read=6232.860
                          ->  Index Scan using idx_document_cause on document _d  (cost=0.00..93.02 rows=1 width=132) (actual time=6.358..11.521 rows=1 loops=1582)
                                Index Cond: ((cause_db_id = 2605) AND (cause_id = _c.cause_id))
                                Filter: ((doctype_db_id = 0) AND ((doctype_id = 213207) OR (doctype_id = 403760)))
                                Rows Removed by Filter: 2
                                Buffers: shared hit=8107 read=2331
                                I/O Timings: read=18169.990
                    ->  Index Scan using participant_idx_cause_id on participant _p  (cost=0.00..155.70 rows=1 width=123) (actual time=9.126..14.978 rows=2 loops=1417)
                          Index Cond: ((cause_id = _d.cause_id) AND (cause_db_id = 2605))
                          Buffers: shared hit=5481 read=2973
                          I/O Timings: read=21175.465
              ->  Index Scan using idx_participant_type__participant_type_id on participant_type _pt  (cost=0.00..4.94 rows=1 width=60) (actual time=0.027..0.029 rows=1 loops=2879)
                    Index Cond: (participant_type_id = _p.participant_type_id)
                    Filter: (((participant_type)::text !~~* '%свідок%'::text) AND (_p.participant_type_db_id = participant_type_db_id))
                    Rows Removed by Filter: 0
                    Buffers: shared hit=5758
Total runtime: 45851.433 ms
...
Рейтинг: 0 / 0
16.02.2016, 11:57
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172164
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Sheriffua
Код: 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.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
QUERY PLAN
GroupAggregate  (cost=5488.63..5488.67 rows=1 width=312) (actual time=45844.031..45850.971 rows=1415 loops=1)
  Buffers: shared hit=19555 read=6266
  I/O Timings: read=45578.315
  ->  Sort  (cost=5488.63..5488.64 rows=1 width=312) (actual time=45844.008..45844.429 rows=2873 loops=1)
        Sort Key: _c.cause_id, _c.number, _d.judge, _d.judges, _c.in_date, _c.is_active
        Sort Method: quicksort  Memory: 947kB
        Buffers: shared hit=19555 read=6266
        I/O Timings: read=45578.315
        ->  Nested Loop  (cost=0.01..5488.62 rows=1 width=312) (actual time=57.880..45820.944 rows=2873 loops=1)
              Buffers: shared hit=19544 read=6266
              I/O Timings: read=45578.315
              ->  Nested Loop  (cost=0.01..5483.67 rows=1 width=268) (actual time=57.842..45720.910 rows=2879 loops=1)
                    Join Filter: (_c.cause_id = _p.cause_id)
                    Buffers: shared hit=13786 read=6266
                    I/O Timings: read=45578.315
                    ->  Nested Loop  (cost=0.01..5327.96 rows=1 width=161) (actual time=40.032..24485.573 rows=1417 loops=1)
                          Buffers: shared hit=8305 read=3293
                          I/O Timings: read=24402.850
                          ->  Index Scan using cause_idx_cause_db_id_in_date on cause _c  (cost=0.01..4769.78 rows=6 width=33) (actual time=0.158..6248.657 rows=1582 loops=1)
                                Index Cond: ((cause_db_id = 2605) AND (in_date >= to_date('01.03.2015'::text, 'dd.mm.yyyy'::text)) AND (in_date <= to_date('31.03.2015'::text, 'dd.mm.yyyy'::text)))
                                Filter: ((deleted <> 1) AND (COALESCE(include_stat, 1::smallint) = 1))
                                Buffers: shared hit=198 read=962
                                I/O Timings: read=6232.860
                          ->  Index Scan using idx_document_cause on document _d  (cost=0.00..93.02 rows=1 width=132) (actual time=6.358..11.521 rows=1 loops=1582)
                                Index Cond: ((cause_db_id = 2605) AND (cause_id = _c.cause_id))
                                Filter: ((doctype_db_id = 0) AND ((doctype_id = 213207) OR (doctype_id = 403760)))
                                Rows Removed by Filter: 2
                                Buffers: shared hit=8107 read=2331
                                I/O Timings: read=18169.990
                    ->  Index Scan using participant_idx_cause_id on participant _p  (cost=0.00..155.70 rows=1 width=123) (actual time=9.126..14.978 rows=2 loops=1417)
                          Index Cond: ((cause_id = _d.cause_id) AND (cause_db_id = 2605))
                          Buffers: shared hit=5481 read=2973
                          I/O Timings: read=21175.465
              ->  Index Scan using idx_participant_type__participant_type_id on participant_type _pt  (cost=0.00..4.94 rows=1 width=60) (actual time=0.027..0.029 rows=1 loops=2879)
                    Index Cond: (participant_type_id = _p.participant_type_id)
                    Filter: (((participant_type)::text !~~* '%свідок%'::text) AND (_p.participant_type_db_id = participant_type_db_id))
                    Rows Removed by Filter: 0
                    Buffers: shared hit=5758
Total runtime: 45851.433 ms


(Мысли вслух.)
Ну, вы сидите на IO. Первый IS, возвращающий 1582 записи (вместо ожидаемых 6) работает аж 6 секунд. Для каждой возвращаемой записи (в среднем) нужна 1 страница, 80% из которых холодные. Диси отдают страницу за 6мс, что хорошо.
Таблица с пометкой CLUSTER, но когда тот калстер был. Кажется, что данные очень фрагментированы.

А приведите, пожалуйста, вывод:
Код: sql
1.
EXPLAIN SELECT * FROM cause;



А можно сделать так (блокирующая операция!):
Код: sql
1.
2.
REINDEX TABLE cause;
CLUSTER TABLE cause USING cause_idx_cause_db_id_in_date;

и попробовать ещё раз?
...
Рейтинг: 0 / 0
16.02.2016, 12:38
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172167
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Sheriffua,

Ну вот 99% времени уходит на работу с диском. Никакие индексы тут не помогут. Да и план запроса вполне нормальный.
Или больше памяти базе выделять или ставить диски быстрее (читай ssd).
(ну или база криво настроена что тоже реальный вариант).
...
Рейтинг: 0 / 0
16.02.2016, 12:41
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172173
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Maxim Boguk...Или больше памяти базе выделять...
Кстати да! Шаренных буферов всего 32 метра — поднять бы...
...
Рейтинг: 0 / 0
16.02.2016, 12:45
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172209
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
vyegorov,

Код: sql
1.
2.
3.
EXPLAIN SELECT * FROM cause;
Результат:
Seq Scan on cause  (cost=0.00..2934368.80 rows=31156480 width=1066)
...
Рейтинг: 0 / 0
16.02.2016, 13:10
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172239
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
vyegorovMaxim Boguk...Или больше памяти базе выделять...
Кстати да! Шаренных буферов всего 32 метра — поднять бы...

Черт я был уверен что там 32GB shared_buffers.
Автор топика - вы базу что на мобильном телефоне 5 летней давности пускаете?????
И потом удивляетесь что все медленно?

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
16.02.2016, 13:29
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172381
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
vyegorovMaxim Boguk...Или больше памяти базе выделять...
Кстати да! Шаренных буферов всего 32 метра — поднять бы...

Откуда узнали что 32 метра?

P.S. Я не админ Postgresql, а пробую решить проблему с оптимизацией запроса, НО админам сообщили по поводу замечания, спасибо.
...
Рейтинг: 0 / 0
16.02.2016, 15:40
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172407
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Sheriffuavyegorovпропущено...

Кстати да! Шаренных буферов всего 32 метра — поднять бы...Откуда узнали что 32 метра?

P.S. Я не админ Postgresql, а пробую решить проблему с оптимизацией запроса, НО админам сообщили по поводу замечания, спасибо
Вы же Excel прикладывали с нестандартным конфигом (лучше текстом вставлять, через [src] и спойлер).

У вас 4096 буфера по 8Кб, вроде 32 метра выходит :)
...
Рейтинг: 0 / 0
16.02.2016, 15:59
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172492
Sergei.Agalakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergei.Agalakov
Участник
qwwqSergei.AgalakovПопробуйте разбить индекс cause_idx_cause_db_id_in_date на два индекса - по дате и по ID."вы не поняли".

селективность cause_db_id -- с гулькин чих.
это ид "базы данных".



Sergei.AgalakovС %asdf% вам обычные индексы не помогут. Смотрите текстовые индексы в Postgres."вы не поняли"

автор уже понаделал необычных триграммов. ан -- оно не подхватывается.
печаль.
[ и не должны были, из общих соображений о межтабличной структуре его OR фильтра.]
Да понял я.
С первым: ожидания оптимизатора расходятся с жизнью больше, чем на два порядка (6 против 1582). Это не есть хорошо, и я предложил путь борьбы с этим.
Со вторым: Я не спорю, что здесь надо бы union all если цель заставить искать по фамилии в первую очередь, но это для скорости запроса по сравнению с поском по номеру документа не поможет скорее всено никак. Но триграммы-то зачем? Они по настоящему нужны только если про текст мало что известно. Ну кому нужен поиск по документам для фамилии %ова% ? Какая там избирательность получится? %Петрова% по обычному текстовому индексу еще куда ни шло...
Оба моих замечания были больше концептуального порядка. Я согласен, что если б не приходилось читать с диска, то и при существующих индексах автор на скорость вряд ли стал бы жаловаться.
...
Рейтинг: 0 / 0
16.02.2016, 17:17
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172875
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
я так понимаю, что увеличение параметра shared_buffers на план запроса не влияет? т.к. админы увеличили его до 512 Мб, но изменений в плане не увидел.
...
Рейтинг: 0 / 0
17.02.2016, 10:06
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172878
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Sheriffua,

план запроса и так нормальный. сколько памяти на сервере?
...
Рейтинг: 0 / 0
17.02.2016, 10:10
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172884
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Sheriffuaя так понимаю, что увеличение параметра shared_buffers на план запроса не влияет? т.к. админы увеличили его до 512 Мб, но изменений в плане не увидел.

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

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
17.02.2016, 10:18
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172890
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
Alexius,

Памяти на сервере 16Гб
...
Рейтинг: 0 / 0
17.02.2016, 10:23
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39172972
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Sheriffua,

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


откуда мораль -- если хотите ускорить и первый запуск -- надо таки переписывать запрос, а то и реорганизовывать данные.

мелочь, которая почти не повлияет -- т.к. количество чтений останется тем же -- это уйти от дурных привычек, типа coalesce(field,value) = value -- т.к. этим вы лишаете оптимизатор возможности воспользоваться индексом, если тот есть. т.е. возможности усечь читаемое с диска множество. этой мелочевки можно ещё найти. но, до тех пор , пока вы не усечёте количество необходимых чтений -- она мало что даст.

если вот эти условия -- сильно усекающие


Код: sql
1.
2.
AND coalesce(_c.include_stat,'1') = 1
AND _c.deleted <> 1


я бы подумал о создании условного индекса с именно таким условием.
Код: sql
1.
2.
3.
4.
CREATE INDEX  ON public.cause
  USING btree (cause_db_id, in_date)
WHERE coalesce(include_stat,'1') = 1
AND deleted <> 1


(закроем глаза на coalesce, и место приращения рук кодера).
если нет -- искал бы в другом месте.

может быть даже там, где октоген, но только с ручным разбиением (по OR) на UNION.
...
Рейтинг: 0 / 0
17.02.2016, 11:29
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39173068
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
qwwq,

Если бы условие
Код: plsql
1.
AND coalesce(_c.include_stat,'1') = 1 AND _c.deleted <> 1


было селективным то в explain analyze было бы что то про:
Rows Removed by Filter: N

в блоке
Код: plaintext
1.
2.
3.
4.
5.
                          ->  Index Scan using cause_idx_cause_db_id_in_date on cause _c  (cost=0.01..4769.78 rows=6 width=33) (actual time=0.158..6248.657 rows=1582 loops=1)
                                Index Cond: ((cause_db_id = 2605) AND (in_date >= to_date('01.03.2015'::text, 'dd.mm.yyyy'::text)) AND (in_date <= to_date('31.03.2015'::text, 'dd.mm.yyyy'::text)))
                                Filter: ((deleted <> 1) AND (COALESCE(include_stat, 1::smallint) = 1))
                                Buffers: shared hit=198 read=962
                                I/O Timings: read=6232.860

Поскольку в explain analyze ничего про это не сказано - то это условие тут на всякий случай/дырку в обоях закрыть и селективность у него нулевая.
Так что или данные в памяти или (в худшем случае на ssd) если надо чтобы быстро, а сам план вполне вменяем для такого запроса.

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

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
17.02.2016, 12:53
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39173144
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Maxim Boguk,

вы правы, но в исходном плане таки 4 записи из 2000 этим фильтром отсечено.


если посмотреть исходный план -- основные потери на фильтрацию тут:

Код: sql
1.
2.
3.
              ->  Nested Loop  (cost=0.01..4337.92 rows=1 width=269) (actual time=108.638..21906.899 rows=2 loops=1)
                    Join Filter: ((_c.cause_id = _p.cause_id) AND (((_c.number)::text ~~* '%Миронович%'::text) OR ((_p.participant)::text ~~* '%Миронович%'::text) OR ((_d.judge)::text ~~* '%Миронович%'::text) OR ((_d.judges)::text ~~* '%Миронович%'::text)))
                    Rows Removed by Join Filter: 2009



т.е. нужно всё же попытаться развалить запрос на 3 (или 2) и запинать на предложенный восьмигеном триграм.

А то, что по горячим оно и так будет в 1000 раз шустрее, при достаточном шареде -- это может автора не устраивать -- у него это может быть редкий запрос, выполняемый обычно по холодным данным.
...
Рейтинг: 0 / 0
17.02.2016, 13:51
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39173145
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Исходный == 18810155
...
Рейтинг: 0 / 0
17.02.2016, 13:52
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39173220
Sheriffua
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sheriffua
Участник
Спасибо всем ответившим.
...
Рейтинг: 0 / 0
17.02.2016, 14:44
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #39173365
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
eще есть варианты:
из бест практис:
1. предрасчеты по месяцам. Регламентно иметь агрегаты по всем фамилиям по месяцам.
сработает если выборки тоже по месяцам и мало изменений в прошедших периодах.
2. денормализации - втянуть в таблицу участников еще и имя судьи и с.number - тогда индекс по нему даст высокую селективность.
минус - усложнит структуру и потенциально создаст неконсистентные данные.

сомнительные варианты:
3. партицирование - только если по тем же месяцам. Тогда вместо рандомных чтений будет последовательный скан партиции
минус - постгрес и партицирование - вместе "веселей".

p.s. кстати можно заменить фильтр в участниках со '%свідок%' на список participant_type_id in (....) .
тогда вообще можно не джоинить participant_type , а participant_type_id добавить в индекс
...
Рейтинг: 0 / 0
17.02.2016, 16:57
| Ответить | Цитировать | Написать  
20 сообщений из 45, страница 2 из 2
  2  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39173145&tid=1997429]:
 0ms
get settings:
 5ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 156ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 42ms
get tp. blocked users:
 1ms
others: 208ms
total:  434ms
созд. / загр.: 434ms
x
x
Закрыть


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