Помогите перенести запрос с mssql на postgresql / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите перенести запрос с mssql на postgresql

25 сообщений из 52, страница 2 из 3

все

Помогите перенести запрос с mssql на postgresql

#39507140

Alexey Trizno

Гость

Maxim Boguk, там совсем нет никакой нагрузки на диск :( вообще. Результат же всего 800 записей, там вообще перебирается их минимум для такой мощности системы. Вот пример статистики (выделен справа кусок, когда постоянно выполняю тестовый запрос, который ровно выполняется за ~800ms):

...

Рейтинг:

0 / 0

17.08.2017, 19:28

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507144

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Alexey Trizno,

800ms - максимальная скорость которая получается при выбранном плане.
Как попробовать изменить план - я написал (поднять default_statistics_target и сделать analyze базы).
Если не поможет - есть варианты как переписать запрос по другому.

...

Рейтинг:

0 / 0

17.08.2017, 19:32

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507151

Alexey Trizno

Гость

Maxim Boguk, вот что вышло на pgsql:

select count(*) from respondents r where project_id = 994;
= 47693

план:

Код: plaintext

1.
2.
3.
4.
5.
6.

"Aggregate  (cost=19162.56..19162.57 rows=1 width=0) (actual time=72.762..72.763 rows=1 loops=1)"
"  ->  Index Only Scan using ix_respondents_projectid_contactid on respondents r  (cost=0.43..19040.03 rows=49010 width=0) (actual time=0.025..44.182 rows=47693 loops=1)"
"        Index Cond: (project_id = 994)"
"        Heap Fetches: 47693"
"Planning time: 0.058 ms"
"Execution time: 72.784 ms"

на mssql (проект другой, т.к. база другая, но похож по сути):

select count(*) from Respondents where ProjectID = 12138
= 50087

на этом проекте в mssql запрос тестовый выполняется примерно 400...500ms, что конечно близко к pgsql, хоть там и всё равно медленнее (можно предположить другую структуру записей, т.к. всё же разные проекты).
но первое выполнение идёт жутко...

...

Рейтинг:

0 / 0

17.08.2017, 19:38

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507153

Alexey Trizno

Гость

Maxim Boguk, если сделать:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

set default_statistics_target=10000;

explain analyze select
    28173, 123, iv.id
from
  interviews iv
  inner join respondents r on (r.id = iv.respondent_id)
  left outer join counter_interviews civ on (civ.counter_id = 28173 and civ.version_idx = 123 and civ.interview_id = iv.id)
where
  r.project_id = 994 and civ.id is null and 
  exists(select id from answers where (interview_id = iv.id and question_number = 999) and (answer_code = 9))

то вроде бы ничего не меняется:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

"Nested Loop  (cost=4059.31..4149.93 rows=1 width=8) (actual time=229.441..1511.011 rows=817 loops=1)"
"  ->  Hash Left Join  (cost=4058.88..4149.45 rows=1 width=16) (actual time=228.926..1025.318 rows=90178 loops=1)"
"        Hash Cond: (iv.id = civ.interview_id)"
"        Filter: (civ.id IS NULL)"
"        ->  Nested Loop  (cost=4056.09..4132.89 rows=3668 width=16) (actual time=228.894..922.216 rows=90178 loops=1)"
"              ->  HashAggregate  (cost=4055.65..4055.95 rows=30 width=8) (actual time=228.872..285.168 rows=90178 loops=1)"
"                    Group Key: answers.interview_id"
"                    ->  Index Scan using ix_answers_questionnumber_answercode on answers  (cost=0.56..4046.48 rows=3668 width=8) (actual time=0.020..138.038 rows=90178 loops=1)"
"                          Index Cond: ((question_number = 999) AND (answer_code = 9))"
"              ->  Index Scan using pk_interviews on interviews iv  (cost=0.43..2.55 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=90178)"
"                    Index Cond: (id = answers.interview_id)"
"        ->  Hash  (cost=2.78..2.78 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=1)"
"              Buckets: 1024  Batches: 1  Memory Usage: 0kB"
"              ->  Index Scan using ix_counterinterviews_counterid_versionidx on counter_interviews civ  (cost=0.56..2.78 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1)"
"                    Index Cond: ((counter_id = 28173) AND (version_idx = 123))"
"  ->  Index Scan using pk_respondents on respondents r  (cost=0.43..0.47 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=90178)"
"        Index Cond: (id = iv.respondent_id)"
"        Filter: (project_id = 994)"
"        Rows Removed by Filter: 1"
"Planning time: 0.606 ms"
"Execution time: 1511.486 ms"

...

Рейтинг:

0 / 0

17.08.2017, 19:40

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507177

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Alexey Trizno,

вам надо сделать default_statistics_target в конфиге базы
далее пречитать конфиг
и сделать analyze; внутри вашей базы чтобы обновить статистику.

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

17.08.2017, 20:27

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507183

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Alexey TriznoMaxim Boguk, вот что вышло на pgsql:

select count(*) from respondents r where project_id = 994;
= 47693

план:

Код: sql

1.
2.
3.
4.
5.
6.

"Aggregate  (cost=19162.56..19162.57 rows=1 width=0) (actual time=72.762..72.763 rows=1 loops=1)"
"  ->  Index Only Scan using .... rows=47693 loops=1)"
"        Index Cond: (project_id = 994)"
"        Heap Fetches: 47693"
"Planning time: 0.058 ms"
"Execution time: 72.784 ms"

...

Рейтинг:

0 / 0

17.08.2017, 20:46

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507185

Alexey Trizno

Гость

Maxim Boguk, сделал в конфиге и релоад. Вот план:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

"Nested Loop  (cost=4081.42..4452.65 rows=1 width=8) (actual time=273.708..1820.367 rows=817 loops=1)"
"  ->  Hash Left Join  (cost=4080.99..4452.17 rows=1 width=16) (actual time=272.691..1232.922 rows=90214 loops=1)"
"        Hash Cond: (iv.id = civ.interview_id)"
"        Filter: (civ.id IS NULL)"
"        ->  Nested Loop  (cost=4078.20..4435.55 rows=3685 width=16) (actual time=272.645..1129.704 rows=90214 loops=1)"
"              ->  HashAggregate  (cost=4077.77..4079.16 rows=139 width=8) (actual time=272.624..330.215 rows=90214 loops=1)"
"                    Group Key: answers.interview_id"
"                    ->  Index Scan using ix_answers_questionnumber_answercode on answers  (cost=0.56..4068.55 rows=3685 width=8) (actual time=0.065..177.529 rows=90214 loops=1)"
"                          Index Cond: ((question_number = 999) AND (answer_code = 9))"
"              ->  Index Scan using pk_interviews on interviews iv  (cost=0.43..2.55 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=90214)"
"                    Index Cond: (id = answers.interview_id)"
"        ->  Hash  (cost=2.78..2.78 rows=1 width=16) (actual time=0.032..0.032 rows=0 loops=1)"
"              Buckets: 1024  Batches: 1  Memory Usage: 0kB"
"              ->  Index Scan using ix_counterinterviews_counterid_versionidx on counter_interviews civ  (cost=0.56..2.78 rows=1 width=16) (actual time=0.032..0.032 rows=0 loops=1)"
"                    Index Cond: ((counter_id = 28173) AND (version_idx = 123))"
"  ->  Index Scan using pk_respondents on respondents r  (cost=0.43..0.47 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=90214)"
"        Index Cond: (id = iv.respondent_id)"
"        Filter: (project_id = 994)"
"        Rows Removed by Filter: 1"
"Planning time: 5.552 ms"
"Execution time: 1821.212 ms"

analyze всей базы тоже уже сделан.

идея с переписыванием запроса может поможет? в чем она заключается?

...

Рейтинг:

0 / 0

17.08.2017, 20:52

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507188

Alexey Trizno

Гость

Maxim Boguk, судя по:

(cost=0.56..4068.55 rows=3685 width=8) (actual time=0.065..177.529 rows=90214 loops=1)

так и есть промах по оценке и реальности?

...

Рейтинг:

0 / 0

17.08.2017, 20:54

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507189

Alexey Trizno

Гость

qwwq, в смысле что всё в памяти и так?

...

Рейтинг:

0 / 0

17.08.2017, 20:54

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507193

Alexey Trizno

Гость

Maxim Boguk, пока пробовал с одним этим проектом - в целом всё стало считаться более менее, разные виды запросов от 15 до 700 ms, что хорошо. Но беру другой проект (база эта же конечно), делаем расчет - и первый же более менее сложный запрос падает с таймаутом (30 сек), потом ещё раз его вручную выполняю - отрабатывает за 20 сек, потом ещё раз - отрабатывает за 600 мс. Как быть?
Где же кэширование и прочие радости? Причём этот запрос был не первый в группе расчета по этому проекту, т.е. данные частично и так уже были прочитаны и где-то должны быть близко. Вот запрос такой, для примера:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

select                                                                                                                    
    40046, 2, iv.id                                                                                                       
from                                                                                                                      
  interviews iv                                                                                                           
  inner join respondents r on (r.id = iv.respondent_id)                                                                   
  left outer join counter_interviews civ on (civ.counter_id = 40046 and civ.version_idx = 2 and civ.interview_id = iv.id) 
where                                                                                                                     
  r.project_id = 1173 and civ.id is null and                                                                              
  ((exists(select id from answers where (interview_id = iv.id and question_number = 502) and (answer_code = 1)) or        
  exists(select id from answers where (interview_id = iv.id and question_number = 502) and (answer_code = 2)) or          
  exists(select id from answers where (interview_id = iv.id and question_number = 502) and (answer_code = 3)) or          
  exists(select id from answers where (interview_id = iv.id and question_number = 502) and (answer_code = 4))) and        
  exists(select id from answers where (interview_id = iv.id and question_number = 599) and (answer_code = 9)))

План:

Код: plaintext

"Nested Loop  (cost=1383.41..1417.73 rows=1 width=8) (actual time=1199.089..1199.089 rows=0 loops=1)"
"  ->  Hash Right Join  (cost=1382.97..1417.25 rows=1 width=16) (actual time=1077.113..1087.901 rows=15978 loops=1)"
"        Hash Cond: (civ.interview_id = iv.id)"
"        Filter: (civ.id IS NULL)"
"        Rows Removed by Filter: 501"
"        ->  Index Scan using ix_counterinterviews_counterid_versionidx on counter_interviews civ  (cost=0.56..34.60 rows=60 width=16) (actual time=0.022..0.767 rows=501 loops=1)"
"              Index Cond: ((counter_id = 40046) AND (version_idx = 2))"
"        ->  Hash  (cost=1372.60..1372.60 rows=785 width=16) (actual time=1075.006..1075.006 rows=16479 loops=1)"
"              Buckets: 1024  Batches: 1  Memory Usage: 773kB"
"              ->  Nested Loop  (cost=931.43..1372.60 rows=785 width=16) (actual time=137.601..1063.448 rows=16479 loops=1)"
"                    ->  HashAggregate  (cost=931.00..931.32 rows=32 width=8) (actual time=137.544..165.171 rows=43934 loops=1)"
"                          Group Key: answers.interview_id"
"                          ->  Index Scan using ix_answers_questionnumber_answercode on answers  (cost=0.56..928.91 rows=837 width=8) (actual time=0.022..85.586 rows=43934 loops=1)"
"                                Index Cond: ((question_number = 599) AND (answer_code = 9))"
"                    ->  Index Scan using pk_interviews on interviews iv  (cost=0.43..13.78 rows=1 width=16) (actual time=0.018..0.019 rows=0 loops=43934)"
"                          Index Cond: (id = answers.interview_id)"
"                          Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4) OR (alternatives: SubPlan 5 or hashed SubPlan 6) OR (alternatives: SubPlan 7 or hashed SubPlan 8))"
"                          Rows Removed by Filter: 1"
"                          SubPlan 1"
"                            ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_1  (cost=0.56..2.79 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=43934)"
"                                  Index Cond: ((interview_id = iv.id) AND (question_number = 502) AND (answer_code = 1))"
"                                  Heap Fetches: 5225"
"                          SubPlan 2"
"                            ->  Bitmap Heap Scan on answers answers_2  (cost=2124.29..135726.70 rows=146451 width=8) (never executed)"
"                                  Recheck Cond: ((question_number = 502) AND (answer_code = 1))"
"                                  ->  Bitmap Index Scan on ix_answers_questionnumber_answercode  (cost=0.00..2087.68 rows=146451 width=0) (never executed)"
"                                        Index Cond: ((question_number = 502) AND (answer_code = 1))"
"                          SubPlan 3"
"                            ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_3  (cost=0.56..2.79 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=38709)"
"                                  Index Cond: ((interview_id = iv.id) AND (question_number = 502) AND (answer_code = 2))"
"                                  Heap Fetches: 3739"
"                          SubPlan 4"
"                            ->  Bitmap Heap Scan on answers answers_4  (cost=1829.06..119544.87 rows=126126 width=8) (never executed)"
"                                  Recheck Cond: ((question_number = 502) AND (answer_code = 2))"
"                                  ->  Bitmap Index Scan on ix_answers_questionnumber_answercode  (cost=0.00..1797.53 rows=126126 width=0) (never executed)"
"                                        Index Cond: ((question_number = 502) AND (answer_code = 2))"
"                          SubPlan 5"
"                            ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_5  (cost=0.56..2.79 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=34970)"
"                                  Index Cond: ((interview_id = iv.id) AND (question_number = 502) AND (answer_code = 3))"
"                                  Heap Fetches: 5559"
"                          SubPlan 6"
"                            ->  Bitmap Heap Scan on answers answers_6  (cost=800.02..56920.98 rows=55137 width=8) (never executed)"
"                                  Recheck Cond: ((question_number = 502) AND (answer_code = 3))"
"                                  ->  Bitmap Index Scan on ix_answers_questionnumber_answercode  (cost=0.00..786.24 rows=55137 width=0) (never executed)"
"                                        Index Cond: ((question_number = 502) AND (answer_code = 3))"
"                          SubPlan 7"
"                            ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_7  (cost=0.56..2.79 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=29411)"
"                                  Index Cond: ((interview_id = iv.id) AND (question_number = 502) AND (answer_code = 4))"
"                                  Heap Fetches: 1956"
"                          SubPlan 8"
"                            ->  Index Scan using ix_answers_questionnumber_answercode on answers answers_8  (cost=0.56..42150.22 rows=39713 width=8) (never executed)"
"                                  Index Cond: ((question_number = 502) AND (answer_code = 4))"
"  ->  Index Scan using pk_respondents on respondents r  (cost=0.43..0.47 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=15978)"
"        Index Cond: (id = iv.respondent_id)"
"        Filter: (project_id = 1173)"
"        Rows Removed by Filter: 1"
"Planning time: 4.456 ms"
"Execution time: 1199.192 ms"

...

Рейтинг:

0 / 0

17.08.2017, 21:05

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507194

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Alexey TriznoMaxim Boguk, судя по:

(cost=0.56..4068.55 rows=3685 width=8) (actual time=0.065..177.529 rows=90214 loops=1)

так и есть промах по оценке и реальности?

"analyze всей базы тоже уже сделан." - уже ПОСЛЕ смены конфига? если нет то его надо повторить.

Ну и для теста проверять просто
explain analyze select * from answers where (question_number = 999) and (answer_code = 9)
насколько база реалистично оценивает результаты

Если не поможет - я напишу как попробовать переписать.

...

Рейтинг:

0 / 0

17.08.2017, 21:06

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507196

Alexey Trizno

Гость

Maxim Boguk, сделал analyze ещё раз. И план такого запроса:

Код: plaintext

1.
2.
3.
4.

"Index Scan using ix_answers_questionnumber_answercode on answers  (cost=0.56..4128.57 rows=3739 width=85) (actual time=0.028..135.732 rows=90221 loops=1)"
"  Index Cond: ((question_number = 999) AND (answer_code = 9))"
"Planning time: 0.065 ms"
"Execution time: 198.531 ms"

Ничего не поменялось.

...

Рейтинг:

0 / 0

17.08.2017, 21:18

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507207

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Alexey Trizno Но беру другой проект (база эта же конечно), делаем расчет - и первый же более менее сложный запрос падает с таймаутом (30 сек), потом ещё раз его вручную выполняю - отрабатывает за 20 сек, потом ещё раз - отрабатывает за 600 мс. Как быть?
Где же кэширование и прочие радости?

Кеширование у Postgresql на уровне страниц с данными. Поэтому те данные к которым не обращались будут медленно и болезненно читаться с механического диска (где для быстрого плана нужен random_page_cost = 10 а не 1.1).
Как быть:
1)ssd чтобы чтение холодных данных быстрее шло (на 2-3 порядка)
2)больше памяти чтобы данные в памяти в основном висели (но первый раз по холодным данным всегда будет медленно).

PS: вот там где 600ms - это данные уже закешированы у базы. Не очень понятно что вас смущает.

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

17.08.2017, 22:29

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507208

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

На счет переписывания запроса - попробуйте вот так вот:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

explain analyze 
WITH _iv AS ( SELECT iv.id 
from
  interviews iv
  inner join respondents r on (r.id = iv.respondent_id)
  left outer join counter_interviews civ on (civ.counter_id = 28173 and civ.version_idx = 123 and civ.interview_id = iv.id)
where
  r.project_id = 994 and civ.id is null and
  )
select
    28173, 123, _iv.id
from _iv
where
  exists(select id from answers where (interview_id = _iv.id and question_number = 999) and (answer_code = 9))

Покажите план может я еще пару вариантов подкину как можно лучше сделать.

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

17.08.2017, 22:32

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507445

Alexey Trizno

Гость

Maxim Boguk, так если для быстрого чтения с диска надо random_page_cost = 10, то зачем мы поставили его в 1.1?
И тут я главное не понимаю - 600ms хорошее время, оно устраивает прекрасно. Не устраивает вдруг просадка в десятки раз, которую я не могу объяснить диском или ещё чем-то, т.к. всё железо быстрое, и даже тупо читая всё с диска не может простой запрос выполняться минуты, это не логично никак (при этом на сервере нулевая загрузка cpu и hdd). Нельзя же принять идею, что вдруг любой простой запрос может сваливаться по таймауту и ломать логику работы приложения вплоть до потери важных данных... это беда совсем. Если 600мс получаем при нормальной работе, то при холодном пуске можно допустить 1200, ну 1800 - но никак не 30000 и более.

Про переписанный запрос - попробовал, по ощущениями выходит медленнее. Первый запуск 10 секунд, последующие где-то 960мс.
План:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

Nested Loop Semi Join  (cost=111748.06..111750.31 rows=1 width=8) (actual time=0.759..958.137 rows=817 loops=1)
  CTE _iv
    ->  Nested Loop Left Join  (cost=1.42..111747.49 rows=1 width=8) (actual time=0.052..543.762 rows=74788 loops=1)
          Join Filter: (civ.interview_id = iv.id)
          Filter: (civ.id IS NULL)
          ->  Nested Loop  (cost=0.86..110815.85 rows=61924 width=8) (actual time=0.034..379.612 rows=74788 loops=1)
                ->  Index Scan using ix_respondents_projectid_contactid on respondents r  (cost=0.43..18592.23 rows=47826 width=8) (actual time=0.016..44.366 rows=47693 loops=1)
                      Index Cond: (project_id = 994)
                ->  Index Only Scan using ix_interviews_respondentid_id on interviews iv  (cost=0.43..1.92 rows=1 width=16) (actual time=0.002..0.004 rows=2 loops=47693)
                      Index Cond: (respondent_id = r.id)
                      Heap Fetches: 74788
          ->  Materialize  (cost=0.56..2.78 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=74788)
                ->  Index Scan using ix_counterinterviews_counterid_versionidx on counter_interviews civ  (cost=0.56..2.78 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=1)
                      Index Cond: ((counter_id = 28173) AND (version_idx = 123))
  ->  CTE Scan on _iv  (cost=0.00..0.02 rows=1 width=8) (actual time=0.055..630.213 rows=74788 loops=1)
  ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers  (cost=0.56..2.79 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=74788)
        Index Cond: ((interview_id = _iv.id) AND (question_number = 999) AND (answer_code = 9))
        Heap Fetches: 817
Planning time: 3.797 ms
Execution time: 959.261 ms

...

Рейтинг:

0 / 0

18.08.2017, 12:10

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507449

Alexey Trizno

Гость

Maxim Boguk, точнее нет - без explain и analyze запрос выполняется за 60мс.

...

Рейтинг:

0 / 0

18.08.2017, 12:12

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507473

Alexey Trizno

Гость

Maxim Boguk, и далее... сразу выполняем старую версию того же запроса (без WITH), и он выполнялся почти 2 минуты :-/ Ничего не понимаю. Данные только что были все прочитаны же... второе выполнение 250 мс. Но 2 минуты!!! Так же нельзя работать совсем :(

Но в любом случае - 60 мс против 250мс, разница огромная. Вариант с WITH похоже более верный тут.

Беру любой другой запрос и опять - первое выполнение длится минуты (5 и более)... даже не смог дождаться окончания выполнения - в ступор уходит всё. Оборвал. Со второй попытке запрос 9 секунд выполнялся. С третьей 556мс. Тот же запрос с WITH работает 192мс.

Итого пока выводы:
1) такие запросы надо делать с WITH, т.к. сам планировщик до этого не может додуматься
2) совсем какая-то пока беда с предсказуемостью работы запросов, время выполнения может вдруг стать бесконечным, без причин

...

Рейтинг:

0 / 0

18.08.2017, 12:31

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507519

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Alexey Triznoqwwq, в смысле что всё в памяти и так?
выходит он вместо IOS тупо IS сделал -- всякую запись из индекса проверил в хипе.

вероятно или
1. цифры случайно совпали в копейку, т.е. и карта видимости старая и цифры в 00 притёрлись
2. постгрес лажает с планом -- проверить планом "select count(1) from respondents r where project_id"
3. я чего то вру (бывает)

но если 46тыс хип-фетчей не забрали много времени -- то все же надо думать все было в памяти.

выводите BUFFERS всегда -- будете понимать , почему времена скачут.

я так думаю: вы нещадно апдейтитесь (мсскл это не вредно, а у пж и индексы пухнут и карта видимости портицца)

и прочтите уже букварь по костам, что ли.

...

Рейтинг:

0 / 0

18.08.2017, 13:38

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507537

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Alexey TriznoMaxim Boguk, и далее... сразу выполняем старую версию того же запроса (без WITH), и он выполнялся почти 2 минуты :-/ Ничего не понимаю. Данные только что были все прочитаны же... второе выполнение 250 мс. Но 2 минуты!!! Так же нельзя работать совсем :(

Но в любом случае - 60 мс против 250мс, разница огромная. Вариант с WITH похоже более верный тут.

Беру любой другой запрос и опять - первое выполнение длится минуты (5 и более)... даже не смог дождаться окончания выполнения - в ступор уходит всё. Оборвал. Со второй попытке запрос 9 секунд выполнялся. С третьей 556мс. Тот же запрос с WITH работает 192мс.

Итого пока выводы:
1) такие запросы надо делать с WITH, т.к. сам планировщик до этого не может додуматься
2) совсем какая-то пока беда с предсказуемостью работы запросов, время выполнения может вдруг стать бесконечным, без причин

Как я уже говорил для анализа ситуации вам надо
1)включить track_io_timing в конфиге базы
2)делать подозрительные запросы через explain (analyze, costs, buffers, timing)
тогда будет видно явно сколько времени проведено в работе с дисками а сколько считалось

PS: время доступа к 1 странице с данными в памяти и на механическом диске отличается на 5 порядков. Поэтому достаточно 0.1% cache miss чтобы запрос замедлился в 100-1000 раз. Это вы и сами на explain (analyze, costs, buffers, timing) увидите.
Методы борьбы - 1)основной - базу на ssd держать которые на 3 порядка быстрее чем механика (и всего на 2 порядка медленнее чем память) 2)выделять базе достаточно shared_buffers чтобы основные данные все таки лежали в кеше базы.

Я почему то думаю что mssql или на ssd дисках живет или ему заметно больше памяти выделено (кстати интересный вариант проверить какая скорость работы у mssql сразу после рестарта сервера когда кеши холодные все).

...

Рейтинг:

0 / 0

18.08.2017, 13:58

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507540

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Alexey TriznoЕсли 600мс получаем при нормальной работе, то при холодном пуске можно допустить 1200, ну 1800 - но никак не 30000 и более.

Скорость случайного доступа к механическому диску и к памяти отличается на 5-6 порядков. Поэтому никаких 1800 vs 600 при холодном запуске вы НИКОГДА не получите. SSD на 3 порядка быстрее поэтому там это будет работать сильно лучше.

Тут надо понимать что random_page_cost=10 приведет к плану который будет оптимально (относительно) выполнятся на холодном пуске но КРАЙНЕ не оптимально выполнятся в ситуации когда данные в памяти (поэтому тут всегда есть tradeoff и вам надо решать что вам важнее и какая часть данных у вас в памяти будет).

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

18.08.2017, 14:05

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507591

Alexey Trizno

Гость

Maxim Boguk, сейчас уже включили тайминги и прочее, вроде логирования длительных запросов. План теперь более детальный, но я его не очень понимаю... прошу помощи, где искать узкие места. И ещё - может есть какой-то правильный генератор конфига для pgsql, который сделает его максимально правильным для конкретного железа? (объем памяти, диски, cpu и прочее), ибо параметров там море, и что именно как лучше поставить - не очевидно.

Запрос (исполнялся 11 минут !!!):

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

explain (analyze, buffers, timing, costs) 
select                                                                                                                    
    40046, 2, iv.id                                                                                                       
from                                                                                                                      
  interviews iv                                                                                                           
  inner join respondents r on (r.id = iv.respondent_id)                                                                   
  left outer join counter_interviews civ on (civ.counter_id = 40046 and civ.version_idx = 2 and civ.interview_id = iv.id) 
where                                                                                                                     
  r.project_id = 1173 and civ.id is null and                                                                              
  ((exists(select id from answers where (interview_id = iv.id and question_number = 502) and (answer_code = 1)) or        
  exists(select id from answers where (interview_id = iv.id and question_number = 502) and (answer_code = 2)) or          
  exists(select id from answers where (interview_id = iv.id and question_number = 502) and (answer_code = 3)) or          
  exists(select id from answers where (interview_id = iv.id and question_number = 502) and (answer_code = 4))) and        
  exists(select id from answers where (interview_id = iv.id and question_number = 599) and (answer_code = 9)))

План с деталями от него:

Код: plaintext

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.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.

Nested Loop Left Join  (cost=3348.61..5126.20 rows=1 width=8) (actual time=80950.330..669127.712 rows=13 loops=1)
  Filter: (civ.id IS NULL)
  Rows Removed by Filter: 501
  Buffers: shared hit=767961 read=129668 dirtied=18
  I/O Timings: read=664304.805
  ->  Nested Loop  (cost=3348.17..5124.51 rows=3 width=8) (actual time=25566.332..665510.797 rows=514 loops=1)
        Buffers: shared hit=762773 read=128763 dirtied=5
        I/O Timings: read=660712.148
        ->  Nested Loop  (cost=3347.74..4714.93 rows=802 width=16) (actual time=22977.026..594172.128 rows=16492 loops=1)
              Buffers: shared hit=707821 read=117648 dirtied=4
              I/O Timings: read=589878.109
              ->  HashAggregate  (cost=3347.31..3347.63 rows=32 width=8) (actual time=22931.922..23000.371 rows=43965 loops=1)
                    Group Key: answers.interview_id
                    Buffers: shared hit=675 read=42841 dirtied=4
                    I/O Timings: read=22105.596
                    ->  Bitmap Heap Scan on answers  (cost=25.33..3345.17 rows=855 width=8) (actual time=66.535..22838.328 rows=43965 loops=1)
                          Recheck Cond: ((question_number = 599) AND (answer_code = 9))
                          Heap Blocks: exact=43342
                          Buffers: shared hit=675 read=42841 dirtied=4
                          I/O Timings: read=22105.596
                          ->  Bitmap Index Scan on ix_answers_questionnumber_answercode  (cost=0.00..25.12 rows=855 width=0) (actual time=47.067..47.067 rows=43965 loops=1)
                                Index Cond: ((question_number = 599) AND (answer_code = 9))
                                Buffers: shared hit=4 read=170
                                I/O Timings: read=37.820
              ->  Index Scan using pk_interviews on interviews iv  (cost=0.43..42.72 rows=1 width=16) (actual time=12.983..12.985 rows=0 loops=43965)
                    Index Cond: (id = answers.interview_id)
                    Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4) OR (alternatives: SubPlan 5 or hashed SubPlan 6) OR (alternatives: SubPlan 7 or hashed SubPlan 8))
                    Rows Removed by Filter: 1
                    Buffers: shared hit=707146 read=74807
                    I/O Timings: read=567772.513
                    SubPlan 1
                      ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_1  (cost=0.56..8.59 rows=1 width=0) (actual time=7.561..7.561 rows=0 loops=43965)
                            Index Cond: ((interview_id = iv.id) AND (question_number = 502) AND (answer_code = 1))
                            Heap Fetches: 5230
                            Buffers: shared hit=138157 read=43151
                            I/O Timings: read=331411.992
                    SubPlan 2
                      ->  Bitmap Heap Scan on answers answers_2  (cost=3771.52..307390.31 rows=146630 width=8) (never executed)
                            Recheck Cond: ((question_number = 502) AND (answer_code = 1))
                            ->  Bitmap Index Scan on ix_answers_questionnumber_answercode  (cost=0.00..3734.87 rows=146630 width=0) (never executed)
                                  Index Cond: ((question_number = 502) AND (answer_code = 1))
                    SubPlan 3
                      ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_3  (cost=0.56..8.59 rows=1 width=0) (actual time=0.807..0.807 rows=0 loops=38735)
                            Index Cond: ((interview_id = iv.id) AND (question_number = 502) AND (answer_code = 2))
                            Heap Fetches: 3743
                            Buffers: shared hit=156077 read=2802
                            I/O Timings: read=30745.561
                    SubPlan 4
                      ->  Bitmap Heap Scan on answers answers_4  (cost=3247.24..281101.03 rows=126310 width=8) (never executed)
                            Recheck Cond: ((question_number = 502) AND (answer_code = 2))
                            ->  Bitmap Index Scan on ix_answers_questionnumber_answercode  (cost=0.00..3215.67 rows=126310 width=0) (never executed)
                                  Index Cond: ((question_number = 502) AND (answer_code = 2))
                    SubPlan 5
                      ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_5  (cost=0.56..8.59 rows=1 width=0) (actual time=1.195..1.195 rows=0 loops=34992)
                            Index Cond: ((interview_id = iv.id) AND (question_number = 502) AND (answer_code = 3))
                            Heap Fetches: 5561
                            Buffers: shared hit=141665 read=4042
                            I/O Timings: read=41641.666
                    SubPlan 6
                      ->  Bitmap Heap Scan on answers answers_6  (cost=1424.10..156904.13 rows=55369 width=8) (never executed)
                            Recheck Cond: ((question_number = 502) AND (answer_code = 3))
                            ->  Bitmap Index Scan on ix_answers_questionnumber_answercode  (cost=0.00..1410.26 rows=55369 width=0) (never executed)
                                  Index Cond: ((question_number = 502) AND (answer_code = 3))
                    SubPlan 7
                      ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_7  (cost=0.56..8.59 rows=1 width=0) (actual time=0.529..0.529 rows=0 loops=29431)
                            Index Cond: ((interview_id = iv.id) AND (question_number = 502) AND (answer_code = 4))
                            Heap Fetches: 1958
                            Buffers: shared hit=118363 read=1460
                            I/O Timings: read=15461.312
                    SubPlan 8
                      ->  Bitmap Heap Scan on answers answers_8  (cost=1024.50..120359.08 rows=39799 width=8) (never executed)
                            Recheck Cond: ((question_number = 502) AND (answer_code = 4))
                            ->  Bitmap Index Scan on ix_answers_questionnumber_answercode  (cost=0.00..1014.56 rows=39799 width=0) (never executed)
                                  Index Cond: ((question_number = 502) AND (answer_code = 4))
        ->  Index Scan using pk_respondents on respondents r  (cost=0.43..0.50 rows=1 width=8) (actual time=4.318..4.318 rows=0 loops=16492)
              Index Cond: (id = iv.respondent_id)
              Filter: (project_id = 1173)
              Rows Removed by Filter: 1
              Buffers: shared hit=54952 read=11115 dirtied=1
              I/O Timings: read=70834.039
  ->  Index Scan using fki_counterinterviews_interviewid on counter_interviews civ  (cost=0.43..0.55 rows=1 width=16) (actual time=4.654..7.031 rows=1 loops=514)
        Index Cond: (interview_id = iv.id)
        Filter: ((counter_id = 40046) AND (version_idx = 2))
        Rows Removed by Filter: 11
        Buffers: shared hit=5188 read=905 dirtied=13
        I/O Timings: read=3592.657
Planning time: 77.223 ms
Execution time: 669127.941 ms

...

Рейтинг:

0 / 0

18.08.2017, 15:31

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507604

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Alexey Trizno,

Ну вот вы сами видите что из 11 минут работы на работу с диском ушло I/O Timings: read=664304.805... все 11 минут.
При этом в Buffers: shared hit=767961 read=129668 dirtied=18
в кеш базы попало где то 80%... мимо кеша 20% и было сделано 130.000 обращений к диску, среднее время 1 обращения к диску 4ms -совершенно стандартная цифра для механического SAS/SATA диска. Т.е. ни чудес ни странностей тут нет.

Тут надо или памяти сильно больше или диск сильно быстрее (SSD).

Дальнейшие консультации по тонкой настройке железа и базы и рекомендации по производительности - выходят за пределы форума :).
Можете например обратится по URL у меня в подписи.

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

18.08.2017, 15:49

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507673

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Maxim BogukAlexey Trizno,

Ну вот вы сами видите что из 11 минут работы на работу с диском ушло I/O Timings: read=664304.805... все 11 минут.
При этом в Buffers: shared hit=767961 read=129668 dirtied=18
в кеш базы попало где то 80%... мимо кеша 20% и было сделано 130.000 обращений к диску, среднее время 1 обращения к диску 4ms -совершенно стандартная цифра для механического SAS/SATA диска. Т.е. ни чудес ни странностей тут нет.

Тут надо или памяти сильно больше или диск сильно быстрее (SSD).

Дальнейшие консультации по тонкой настройке железа и базы и рекомендации по производительности - выходят за пределы форума :).
Можете например обратится по URL у меня в подписи.

--
Maxim Boguk
dataegret.ru

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.

SubPlan 1
                      ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_1  (cost=0.56..8.59 rows=1 width=0) (actual time=7.561..7.561 rows=0 loops=43965)
                            Index Cond: ((interview_id = iv.id) AND (question_number = 502) AND (answer_code = 1))
                            Heap Fetches: 5230
                            Buffers: shared hit=138157 read=43151
                            I/O Timings: read=331411.992
-- и так 5 раз
                    SubPlan 2

остывает индекс на больших выборках.

нужно что-то типа full index scan

https://postgrespro.ru/development/tasks/56601
совсем не обязательно читать рендомно, если известно, что надо прочитать много из.

а пока, думается, можно проверить идею такого индекса:

Код: sql

( question_number, answer_code,interview_id);

на предмет лучших ожыданий по попаданию на уже прочитанное.

...

Рейтинг:

0 / 0

18.08.2017, 17:03

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507703

Alexey Trizno

Гость

qwwq, пока сделали замер скорости дисков, вышло не очень радужно: линейное чтение 170Mb/s, а рандомное чуть не в 200 раз медленнее. Но на машине 32Gb памяти, туда по идее должно влезть бОльшая часть основных данных, а уж рассматриваемые 4 таблицы вообще целиком вместе с индексами несколько раз. Зачем вообще на диск лезть для чтения?

...

Рейтинг:

0 / 0

18.08.2017, 18:25

| Ответить | Цитировать | Написать

Помогите перенести запрос с mssql на postgresql

#39507717

Sergei.Agalakov

Участник

Сообщения: 575

Рейтинг: 0 / 0

Потому что читается только то, что запрашивается. Даже если вы 'разогреете' данные сделав full scan на всех таблицах так что они пойдут в память, то и тогда эти данные могут со временем быть выгружены из кэша. Постгрес довольно трудно будет заставить закэшировать все таблицы и индексы до первого рабочего вызова.

...

Рейтинг:

0 / 0

18.08.2017, 19:15

| Ответить | Цитировать | Написать

25 сообщений из 52, страница 2 из 3

все

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите перенести запрос с mssql на postgresql

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

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

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=39507540&tid=1996284]:	0ms
get settings:	9ms
get forum list:	11ms
check forum access:	4ms
check topic access:	4ms
track hit:	29ms
get topic data:	8ms
get forum data:	2ms
get page messages:	50ms
get tp. blocked users:	1ms
others:	13ms

total:	131ms