ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите перенести запрос с mssql на postgresql
52 сообщений из 52, показаны все 3 страниц
  все  
Помогите перенести запрос с mssql на postgresql
    #39505175
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Добрый день!

Что-то при переводе базы с mssql на postgresql упёрся в странную проблему, которой не было на mssql вовсе.
Есть запрос вида (генерируется автоматически по свободным выражениям, которые пишет пользователь):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select
  iv.id
from
  interviews iv
  inner join respondents r on (r.id = iv.respondent_id)
where
  r.project_id = 994 and 
  (exists(select 1 from answers where (interview_id = iv.id and question_number = 905) and (answer_code >= 2 and answer_code <= 7)) and
  (exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 1)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 2)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 6)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 999))))



Структура подчинения таблиц проста: respondents -> interviews -> answers.
mssql на таком запросе всё как-то сам прекрасно оптимизировал, что не приводило к скану таблицы answers - из неё рассматривались только нужные строки.
Тут важный момент - в answers хранится бесконечное кол-во записей, и скан по ней это вообще не вариант. Также как по respondents или interviews.

Если убрать первый exists с проверкой (answer_code >= 2 and answer_code <= 7) - всё становится чуть лучше, но не кординально, да
и нельзя убрать такое - пользователь может написать любое условие, куда более сложное.

Главное непонимание - почему сервер начинает сканировать все записи answers? Чего в принципе никогда делать не будет mssql.
Нынешний план запроса:

Код: 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.
Nested Loop  (cost=1093588.39..1095414.16 rows=31 width=8) (actual time=5172.814..5172.814 rows=0 loops=1)
  ->  Nested Loop  (cost=1093587.96..1093885.05 rows=2996 width=16) (actual time=5018.490..5172.403 rows=42 loops=1)
        ->  HashAggregate  (cost=1093587.53..1093587.60 rows=7 width=8) (actual time=5017.581..5022.039 rows=6987 loops=1)
              Group Key: answers.interview_id
              ->   Seq Scan on answers   (cost=0.00..1093579.54 rows=3196 width=8) (actual time=603.786..5007.788 rows=7157 loops=1)
                    Filter: ((answer_code >= 2) AND (answer_code <= 7) AND (question_number = 905))
                    Rows Removed by Filter: 37697919
        ->  Index Scan using pk_interviews on interviews iv  (cost=0.43..42.48 rows=1 width=16) (actual time=0.020..0.020 rows=0 loops=6987)
              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..8.59 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=6987)
                      Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 1))
                      Heap Fetches: 0
              SubPlan 2
                ->  Seq Scan on answers answers_2  (cost=0.00..999316.17 rows=1642 width=8) (never executed)
                      Filter: ((question_number = 90202) 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.002..0.002 rows=0 loops=6987)
                      Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 2))
                      Heap Fetches: 42
              SubPlan 4
                ->  Seq Scan on answers answers_4  (cost=0.00..999316.17 rows=1466 width=8) (never executed)
                      Filter: ((question_number = 90202) 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=0.002..0.002 rows=0 loops=6945)
                      Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 6))
                      Heap Fetches: 0
              SubPlan 6
                ->  Seq Scan on answers answers_6  (cost=0.00..999316.17 rows=157 width=8) (never executed)
                      Filter: ((question_number = 90202) AND (answer_code = 6))
              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.002..0.002 rows=0 loops=6945)
                      Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 999))
                      Heap Fetches: 0
              SubPlan 8
                ->  Seq Scan on answers answers_8  (cost=0.00..999316.17 rows=3 width=8) (never executed)
                      Filter: ((question_number = 90202) AND (answer_code = 999))
  ->  Index Scan using pk_respondents on respondents r  (cost=0.43..0.50 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=42)
        Index Cond: (id = iv.respondent_id)
        Filter: (project_id = 994)
        Rows Removed by Filter: 1
Planning time: 0.586 ms
Execution time: 5172.899 ms

Что ещё больше меня смутило (вероятно я совсем не понял, как работает оптимизатор в pg), что такой запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
select
  iv.id
from
  interviews iv
  inner join respondents r on (r.id = iv.respondent_id)
where
  r.project_id = 994



тоже приводит чуть ли не к сканам, хотя конечно есть индекс по respondents.project_id и вроде бы свести джоин сервер должен без прохода по всей interviews. План:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Hash Join  (cost=56078.01..295831.44 rows=62631 width=8) (actual time=6754.922..8506.906 rows=74788 loops=1)
  Hash Cond: (iv.respondent_id = r.id)
  ->   Seq Scan on interviews  iv  (cost=0.00..177924.56 rows=6120256 width=16) (actual time=0.006..3748.275 rows=6085512 loops=1)
  ->  Hash  (cost=55475.02..55475.02 rows=48239 width=8) (actual time=72.542..72.542 rows=47693 loops=1)
        Buckets: 8192  Batches: 1  Memory Usage: 1864kB
        ->  Bitmap Heap Scan on respondents r  (cost=1678.28..55475.02 rows=48239 width=8) (actual time=3.792..41.898 rows=47693 loops=1)
              Recheck Cond: (project_id = 994)
              Heap Blocks: exact=3059
              ->  Bitmap Index Scan on ix_respondents_projectid_contactid  (cost=0.00..1666.22 rows=48239 width=0) (actual time=3.467..3.467 rows=47735 loops=1)
                    Index Cond: (project_id = 994)
Planning time: 0.225 ms
Execution time: 8546.951 ms

Направьте пожалуйста, куда копать? Чувствую что ошибка в подходе, т.к. имеется большое наследие по работе с mssql, который очень терпим к решению задачи в лоб.
...
Рейтинг: 0 / 0
14.08.2017, 17:57
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505209
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ролг Хупин
Участник
"Чего в принципе никогда делать не будет mssql."

даладно и не такое бывает.

Структура таблиц и индексов?
...
Рейтинг: 0 / 0
14.08.2017, 18:22
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505237
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Alexey Trizno,

Давайте с второго запроса начнем. Он проще.
Индекс по iv.respondent_id есть?
Какой размер базы и какой размер effective_cache_size в конфиге базы?
И главное какие стоят random_page_cost и seq_page_cost?

Если у вас там где вы ожидаете получается seq scan вместо index scan - наиболее вероятны 2 случая
1)нет индекса
или
2)неверно настроена база в районе тех параметров что я указал.

PS: далеко не факт что в случае 2го запроса если nested loop будет попадать на таблицу interviews которая холодная и лежит на медленных механических дисках что index scan будет быстрее чем seq scan.

По первому запросу я бы добавил индекс на answers(question_number, answer_code) и возможно (смотря на результаты) еще индекс на answers(interview_id, question_number, answer_code) если их нет.
...
Рейтинг: 0 / 0
14.08.2017, 18:50
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505513
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Ниже структура таблиц. Поля не имеющие отношения к вопросу не убирал, а то вдруг...:

Код: 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.
CREATE TABLE public.respondents
(
  id bigint NOT NULL DEFAULT nextval('respondents_id_seq'::regclass),
  created timestamp without time zone NOT NULL DEFAULT timezone('UTC'::text, now()),
  project_id bigint NOT NULL,
  last_interview_id bigint,
  contact_id bigint,
  CONSTRAINT pk_respondents PRIMARY KEY (id),
  CONSTRAINT fk_respondents_contactid FOREIGN KEY (contact_id)
      REFERENCES public.contacts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_respondents_projectid FOREIGN KEY (project_id)
      REFERENCES public.projects (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX fki_respondents_contactid
  ON public.respondents
  USING btree
  (contact_id);

CREATE UNIQUE INDEX ix_respondents_projectid_contactid
  ON public.respondents
  USING btree
  (project_id, contact_id);

Код: 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.
89.
90.
91.
CREATE TABLE public.interviews
(
  id bigint NOT NULL DEFAULT nextval('interviews_id_seq'::regclass),
  created timestamp without time zone NOT NULL DEFAULT timezone('UTC'::text, now()),
  respondent_id bigint NOT NULL,
  user_id bigint NOT NULL,
  started timestamp without time zone NOT NULL,
  ended timestamp without time zone NOT NULL,
  duration integer NOT NULL,
  exported timestamp without time zone,
  contact_phone_id bigint,
  redial_date timestamp without time zone,
  redial_phone character varying(254),
  redial_comment character varying(512),
  user_host_address character varying(50),
  user_agent_id bigint,
  tablet_unique_id character varying(50),
  record_file_name character varying(200),
  latitude double precision,
  longitude double precision,
  address character varying(200),
  validation_comment text,
  validation_interview_id bigint,
  validation_user_id bigint,
  validation_started timestamp without time zone,
  validation_ended timestamp without time zone,
  validation_duration integer,
  CONSTRAINT pk_interviews PRIMARY KEY (id),
  CONSTRAINT fk_interviews_contactphoneid FOREIGN KEY (contact_phone_id)
      REFERENCES public.contact_phones (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_interviews_respondentid FOREIGN KEY (respondent_id)
      REFERENCES public.respondents (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_interviews_useragentid FOREIGN KEY (user_agent_id)
      REFERENCES public.user_agents (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL,
  CONSTRAINT fk_interviews_userid FOREIGN KEY (user_id)
      REFERENCES public.users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_interviews_validationinterviewid FOREIGN KEY (validation_interview_id)
      REFERENCES public.interviews (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_interviews_validationuserid FOREIGN KEY (validation_user_id)
      REFERENCES public.users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX fki_interviews_contactphoneid
  ON public.interviews
  USING btree
  (contact_phone_id);

CREATE INDEX fki_interviews_respondentid
  ON public.interviews
  USING btree
  (respondent_id);

CREATE INDEX fki_interviews_useragentid
  ON public.interviews
  USING btree
  (user_agent_id);

CREATE INDEX fki_interviews_userid
  ON public.interviews
  USING btree
  (user_id);

CREATE INDEX fki_interviews_validationinterviewid
  ON public.interviews
  USING btree
  (validation_interview_id);

CREATE INDEX fki_interviews_validationuserid
  ON public.interviews
  USING btree
  (validation_user_id);

CREATE INDEX ix_interviews_tabletuniqueid
  ON public.interviews
  USING btree
  (tablet_unique_id COLLATE pg_catalog."default");

CREATE TRIGGER tg_interviews_afterinsert
  AFTER INSERT
  ON public.interviews
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_respondent_lastinterviewid();

Код: 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.
CREATE TABLE public.answers
(
  id bigint NOT NULL DEFAULT nextval('answers_id_seq'::regclass),
  created timestamp without time zone NOT NULL DEFAULT timezone('UTC'::text, now()),
  interview_id bigint NOT NULL,
  question_number bigint NOT NULL,
  row_code bigint,
  answer_code bigint,
  open_value_num numeric(18,2),
  open_value_txt text,
  order_idx integer NOT NULL,
  CONSTRAINT pk_answers PRIMARY KEY (id),
  CONSTRAINT fk_answers_interviewid FOREIGN KEY (interview_id)
      REFERENCES public.interviews (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE INDEX fki_answers_interviewid
  ON public.answers
  USING btree
  (interview_id);

CREATE INDEX ix_answers_interviewid_questionnumber_answercode
  ON public.answers
  USING btree
  (interview_id, question_number, answer_code);
...
Рейтинг: 0 / 0
15.08.2017, 12:06
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505526
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Maxim Boguk, размер базы сейчас 50Gb, но это она ещё "молодая" :)

effective_cache_size = 16Gb

а эти дефолтные:
#random_page_cost = 4.0
#seq_page_cost = 1.0

На сервере базы 32Gb памяти.
...
Рейтинг: 0 / 0
15.08.2017, 12:15
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505529
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Кол-во строк в таблицах на данный момент:

answers: 37 687 200
interviews: 5 799 250
respondents: 4 578 730
...
Рейтинг: 0 / 0
15.08.2017, 12:18
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505565
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
Alexey Trizno
Код: sql
1.
2.
3.
4.
5.
  (exists(select 1 from answers where (interview_id = iv.id and question_number = 905) and (answer_code >= 2 and answer_code <= 7)) and
  (exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 1)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 2)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 6)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 999))))


перепиши четыре из existsов в один. Можно и одним обращением к answers обойтись, но вряд ли это улучшит ситуацию.
...
Рейтинг: 0 / 0
15.08.2017, 13:02
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505579
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Alexey TriznoMaxim Boguk, размер базы сейчас 50Gb, но это она ещё "молодая" :)

effective_cache_size = 16Gb

а эти дефолтные:
#random_page_cost = 4.0
#seq_page_cost = 1.0

На сервере базы 32Gb памяти.

покажите планы (оба--два) после
SET LOCAL random_page_cost TO 2.0;


интересно, а ежели писать
Код: sql
1.
2.
3.
4.
answer_code IN (2,3,4,5,6,7)
--OR: answer_code=ANY (ARRAY[2,3,4,5,6,7])
-- вместо
answer_code >= 2 and answer_code <= 7


-- ?
...
Рейтинг: 0 / 0
15.08.2017, 13:30
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505746
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Alexey TriznoMaxim Boguk, размер базы сейчас 50Gb, но это она ещё "молодая" :)

effective_cache_size = 16Gb

а эти дефолтные:
#random_page_cost = 4.0
#seq_page_cost = 1.0

На сервере базы 32Gb памяти.

А shared_buffers сколько?
Вообще поставить random_page_cost=1.1
и сделать analyze всей базы (на всякий случай)

а потом проверить оба запроса
...
Рейтинг: 0 / 0
15.08.2017, 15:46
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505749
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Alexey Trizno,

покажите вывод (в psql)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
\dt+ respondents
\di+ ix_respondents_projectid_contactid

\dt+ interviews
\di+ fki_interviews_respondentid

\dt+ answers
\di+ ix_answers_interviewid_questionnumber_answercode

set enable_seqscan = off;
explain (analyze, buffers) select
  iv.id
from
  interviews iv
  inner join respondents r on (r.id = iv.respondent_id)
where
  r.project_id = 994;

select attname, null_frac, n_distinct, most_common_freqs, most_common_vals from pg_stats where tablename = 'answers' and attname in ('interview_id', 'question_number', 'answer_code');


диски ssd на сервере? если да - то стоит попробовать уменьшить random_page_cost как выше писали.

возможно для исходного запроса я бы добавил индекс в answers по (question_number, answer_code), с которым запрос с указанными параметрами будет быстро работать.
...
Рейтинг: 0 / 0
15.08.2017, 15:49
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505804
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Maxim Boguk, shared_buffers = 8192Mb


qwwq, вот запросы и планы:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SET LOCAL random_page_cost TO 2.0;

explain select
  iv.id
from
  interviews iv
  inner join respondents r on (r.id = iv.respondent_id)
where
  r.project_id = 994;



Код: plaintext
1.
2.
3.
4.
5.
Nested Loop  (cost=0.86..187782.21 rows=63074 width=8)
  ->  Index Scan using ix_respondents_projectid_contactid on respondents r  (cost=0.43..33347.14 rows=48518 width=8)
        Index Cond: (project_id = 994)
  ->  Index Scan using fki_interviews_respondentid on interviews iv  (cost=0.43..3.16 rows=2 width=16)
        Index Cond: (respondent_id = r.id)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SET LOCAL random_page_cost TO 2.0;

explain select
  iv.id
from
  interviews iv
  inner join respondents r on (r.id = iv.respondent_id)
where
  r.project_id = 994 and 
  (exists(select 1 from answers where (interview_id = iv.id and question_number = 905) and (answer_code >= 2 and answer_code <= 7)) and
  (exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 1)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 2)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 6)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 999))))



Код: 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.
Nested Loop  (cost=852626.09..854252.21 rows=31 width=8)
  ->  Nested Loop  (cost=852625.66..852783.87 rows=3011 width=16)
        ->  HashAggregate  (cost=852625.23..852625.30 rows=7 width=8)
              Group Key: answers.interview_id
              ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers  (cost=0.56..852617.20 rows=3212 width=8)
                    Index Cond: ((question_number = 905) AND (answer_code >= 2) AND (answer_code <= 7))
        ->  Index Scan using pk_interviews on interviews iv  (cost=0.43..22.64 rows=1 width=16)
              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))
              SubPlan 1
                ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_1  (cost=0.56..4.59 rows=1 width=0)
                      Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 1))
              SubPlan 2
                ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_2  (cost=0.56..756478.32 rows=1651 width=8)
                      Index Cond: ((question_number = 90202) AND (answer_code = 1))
              SubPlan 3
                ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_3  (cost=0.56..4.59 rows=1 width=0)
                      Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 2))
              SubPlan 4
                ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_4  (cost=0.56..756321.30 rows=1474 width=8)
                      Index Cond: ((question_number = 90202) AND (answer_code = 2))
              SubPlan 5
                ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_5  (cost=0.56..4.59 rows=1 width=0)
                      Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 6))
              SubPlan 6
                ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_6  (cost=0.56..755149.07 rows=158 width=8)
                      Index Cond: ((question_number = 90202) AND (answer_code = 6))
              SubPlan 7
                ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_7  (cost=0.56..4.59 rows=1 width=0)
                      Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 999))
              SubPlan 8
                ->  Index Only Scan using ix_answers_interviewid_questionnumber_answercode on answers answers_8  (cost=0.56..755011.23 rows=3 width=8)
                      Index Cond: ((question_number = 90202) AND (answer_code = 999))
  ->  Index Scan using pk_respondents on respondents r  (cost=0.43..0.48 rows=1 width=8)
        Index Cond: (id = iv.respondent_id)
        Filter: (project_id = 994)
...
Рейтинг: 0 / 0
15.08.2017, 16:37
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505809
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
p2., переписывание exist'ов или какая-то их оптимизация в этом конкретном случае - особо не поможет, т.к. как я написал - этот запрос генерируется по произвольному выражению и юзер может написать что угодно, используя AND/OR/NOT, скобки и любые логические операции.
...
Рейтинг: 0 / 0
15.08.2017, 16:39
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505821
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Alexius, диски на сервере не ssd, но там совсем не видно никакой активной работы с дисками. Тишина. Да и не такая большая база, вообще всё в кэши могло бы влезть. Вывод запросов ниже:

Код: 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.
\dt+ respondents
                       List of relations
 Schema |    Name     | Type  |  Owner   |  Size  | Description
--------+-------------+-------+----------+--------+-------------
 public | respondents | table | postgres | 409 MB |

\di+ ix_respondents_projectid_contactid
                                          List of relations
 Schema |                Name                | Type  |  Owner   |    Table    |  Size  | Description
--------+------------------------------------+-------+----------+-------------+--------+-------------
 public | ix_respondents_projectid_contactid | index | postgres | respondents | 249 MB |

\dt+ interviews
                       List of relations
 Schema |    Name    | Type  |  Owner   |  Size  | Description
--------+------------+-------+----------+--------+-------------
 public | interviews | table | postgres | 919 MB |

\di+ fki_interviews_respondentid
                                      List of relations
 Schema |            Name             | Type  |  Owner   |   Table    |  Size  | Description
--------+-----------------------------+-------+----------+------------+--------+-------------
 public | fki_interviews_respondentid | index | postgres | interviews | 200 MB |

\dt+ answers
                      List of relations
 Schema |  Name   | Type  |  Owner   |  Size   | Description
--------+---------+-------+----------+---------+-------------
 public | answers | table | postgres | 3407 MB |

\di+ ix_answers_interviewid_questionnumber_answercode
                                               List of relations
 Schema |                       Name                       | Type  |  Owner   |  Table  |  Size   | Description
--------+--------------------------------------------------+-------+----------+---------+---------+-------------
 public | ix_answers_interviewid_questionnumber_answercode | index | postgres | answers | 1469 MB |


 QUERY PLAN                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1684.89..340786.21 rows=63080 width=8) (actual time=3.316..364.392 rows=74788 loops=1)
   Buffers: shared hit=220703
   ->  Bitmap Heap Scan on respondents r  (cost=1684.46..55794.20 rows=48520 width=8) (actual time=3.297..40.642 rows=47693 loops=1)
         Recheck Cond: (project_id = 994)
         Heap Blocks: exact=3059
         Buffers: shared hit=3406
         ->  Bitmap Index Scan on ix_respondents_projectid_contactid  (cost=0.00..1672.33 rows=48520 width=0) (actual time=2.971..2.971 rows=47693 loops=1)
               Index Cond: (project_id = 994)
               Buffers: shared hit=347
   ->  Index Scan using fki_interviews_respondentid on interviews iv  (cost=0.43..5.85 rows=2 width=16) (actual time=0.002..0.004 rows=2 loops=47693)
         Index Cond: (respondent_id = r.id)
         Buffers: shared hit=217297
 Planning time: 0.208 ms
 Execution time: 405.779 ms
(14 rows)


     attname     | null_frac | n_distinct |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               most_common_freqs                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |                                                                                                                                                                                                                                                                                                                                                                                               most_common_vals                                                                                                                                                                                                                                                                                                                                                                                             
-----------------+-----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 answer_code     |      0.07 |        321 | {0.216,0.192867,0.0836667,0.0627333,0.0439333,0.0206333,0.0192333,0.0191667,0.0188333,0.0167,0.0161,0.016,0.0157,0.0117667,0.0113,0.00756667,0.00696667,0.00646667,0.00453333,0.00453333,0.00443333,0.00423333,0.00416667}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | {1,2,3,4,5,6,504,8,9,502,7,530,0,503,10,99,11,12,13,14,18,15,16}
 interview_id    |         0 |      71514 | {0.000233333,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333} | {3314826,389946,1596713,1611837,2922195,3114420,3409690,3428710,113980,361394,701288,1163444,1249600,1615981,1620408,1660345,1825929,1881570,1911427,1977349,2083766,2408272,2712194,2718264,2911247,3015868,3158376,3313450,3329786,3413429,3466092,3502418,3516839,3590620,3707100,3818064,3983475,4085482,4445003,4628179,5002412,5020904,5115203,5198701,5242918,5589786,5653325,16199,52639,62905,202398,223954,224779,368358,399761,517626,518806,519587,520987,842725,850958,1006150,1092558,1133196,1136966,1138833,1159268,1194571,1211360,1220815,1258263,1268642,1278146,1296554,1296567,1296921,1307501,1504369,1552388,1600941,1601790,1611100,1617897,1649208,1664544,1679380,1682750,1718023,1725083,1730871,1746927,1778218,1781701,1788502,1791039,1842428,1856001,1875805,1894718,1897524}
 question_number |         0 |       1128 | {0.163633,0.0356667,0.0247667,0.0207333,0.0182667,0.0170667,0.0168333,0.0160667,0.0155667,0.0153333,0.0153333,0.0151333,0.0142667,0.0133,0.0124667,0.0123333,0.0114,0.0112333,0.0111667,0.0106,0.00916667,0.00903333,0.00866667,0.00853333,0.00836667,0.00833333,0.00803333,0.00793333,0.00776667,0.0077,0.00756667,0.00673333,0.0067,0.00663333,0.0065,0.00573333,0.00526667,0.00516667,0.0051,0.00503333,0.00483333,0.00473333,0.00473333,0.00466667,0.0046,0.00456667,0.0044,0.00413333,0.00403333,0.00383333,0.0037,0.0037,0.00363333,0.0036,0.0036,0.00356667,0.00353333,0.00353333,0.00353333,0.0035,0.00346667,0.00346667,0.00336667,0.0033,0.00326667,0.00326667,0.00323333,0.0029,0.0027,0.0026,0.0026,0.00256667,0.00253333,0.00253333,0.00253333,0.0025,0.00246667,0.00236667,0.0023,0.00223333,0.00223333,0.00223333,0.0022,0.0022,0.00216667,0.00216667,0.00216667,0.00216667,0.00216667,0.00216667,0.00206667,0.00203333,0.002,0.00196667,0.00193333,0.00186667,0.00186667,0.0018,0.00176667,0.00176667}                                                                                                                                                                                         | {500,1001,1002,1,2,502,4,504,1000,3,530,503,5,7,10,6,11,9,8,12,501,13,23,20,16,17,14,15,22,539,18,21,24,19,900,999,1003,102,505,901,100,25,26,101,32,28,34,902,31,1007,29,35,36,27,37,30,105,1006,6001,201,38,551,903,103,45,907,6003,904,6002,203,550,998,133,906,1008,1005,507,3000,303,42,106,214,33,108,39,40,51,116,145,1004,549,117,65,41,124,44,53,104,109,204}
(3 rows)
...
Рейтинг: 0 / 0
15.08.2017, 16:49
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505842
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Alexey Trizno,

можно еще результат запроса
Код: sql
1.
select count(distinct interview_id) from answers;

?
...
Рейтинг: 0 / 0
15.08.2017, 17:08
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505852
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Alexius, результат = 3758019
...
Рейтинг: 0 / 0
15.08.2017, 17:22
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505860
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Alexey Trizno,

ок, рекомендации такие:
1) добавить индекс в answers по (question_number, answer_code) - должно решить проблему с 1м запросом
2) сделать
Код: sql
1.
2.
alter table answers alter column interview_id set statistics 500;
analyze answers;



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

дальше еще раз посмотреть план исходного запроса
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
explain analyze select
  iv.id
from
  interviews iv
  inner join respondents r on (r.id = iv.respondent_id)
where
  r.project_id = 994 and 
  (exists(select 1 from answers where (interview_id = iv.id and question_number = 905) and (answer_code >= 2 and answer_code <= 7)) and
  (exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 1)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 2)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 6)) or
  exists(select 1 from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 999))))



3) уменьшить random_page_cost в конфиге с 4 до 1.1 (после изменения стоит понаблюдать за нагрузкой, т.к. какие-то запросы могут в теории хуже выполняться. но скорей всего все нужное в памяти будет). - должно решить проблему со 2м запросом
4) можно еще добавить индекс по (respondent_id, id) в interviews, если в запросах действительно только id выбирается, чтобы использовался index only scan
...
Рейтинг: 0 / 0
15.08.2017, 17:48
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39505867
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Alexius, спасибо, попробую. Результаты напишу.
...
Рейтинг: 0 / 0
15.08.2017, 17:54
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39506379
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Alexius, всё сделал, стало значительно лучше, на мой взгляд:

Код: 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.
Nested Loop  (cost=1934.85..2919.98 rows=17 width=8) (actual time=383.349..383.349 rows=0 loops=1)
  ->  Nested Loop  (cost=1934.42..2140.60 rows=1632 width=16) (actual time=21.363..382.919 rows=46 loops=1)
        ->  HashAggregate  (cost=1933.99..1934.14 rows=15 width=8) (actual time=19.577..23.924 rows=7120 loops=1)
              Group Key: answers.interview_id
              ->  Index Scan using ix_answers_questionnumber_answercode on answers  (cost=0.56..1929.63 rows=1741 width=8) (actual time=0.019..12.512 rows=7290 loops=1)
                    Index Cond: ((question_number = 905) AND (answer_code >= 2) AND (answer_code <= 7))
        ->  Index Scan using pk_interviews on interviews iv  (cost=0.43..13.75 rows=1 width=16) (actual time=0.049..0.049 rows=0 loops=7120)
              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=7120)
                      Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 1))
                      Heap Fetches: 0
              SubPlan 2
                ->  Index Scan using ix_answers_questionnumber_answercode on answers answers_2  (cost=0.56..1024.10 rows=924 width=8) (never executed)
                      Index Cond: ((question_number = 90202) 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.002..0.002 rows=0 loops=7120)
                      Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 2))
                      Heap Fetches: 46
              SubPlan 4
                ->  Index Scan using ix_answers_questionnumber_answercode on answers answers_4  (cost=0.56..889.55 rows=802 width=8) (never executed)
                      Index Cond: ((question_number = 90202) 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.002..0.002 rows=0 loops=7074)
                      Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 6))
                      Heap Fetches: 0
              SubPlan 6
                ->  Index Scan using ix_answers_questionnumber_answercode on answers answers_6  (cost=0.56..93.21 rows=83 width=8) (never executed)
                      Index Cond: ((question_number = 90202) AND (answer_code = 6))
              SubPlan 7
                ->  Index Scan using ix_answers_questionnumber_answercode on answers answers_7  (cost=0.56..2.79 rows=1 width=0) (actual time=0.032..0.032 rows=0 loops=7074)
                      Index Cond: ((question_number = 90202) AND (answer_code = 999))
                      Filter: (interview_id = iv.id)
                      Rows Removed by Filter: 88
              SubPlan 8
                ->  Index Scan using ix_answers_questionnumber_answercode on answers answers_8  (cost=0.56..2.78 rows=1 width=8) (never executed)
                      Index Cond: ((question_number = 90202) AND (answer_code = 999))
  ->  Index Scan using pk_respondents on respondents r  (cost=0.43..0.47 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=46)
        Index Cond: (id = iv.respondent_id)
        Filter: (project_id = 994)
        Rows Removed by Filter: 1
Planning time: 0.808 ms
Execution time: 383.435 ms
...
Рейтинг: 0 / 0
16.08.2017, 14:55
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39506466
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Alexey Trizno,

Думаю что принципиально быстрее чем сейчас это можно будет сделать быстрее только когда 10.0 выйдет (да и то сильно не факт).
Вполне нормальная сейчас производительность получилась.

--
Maxim Boguk
dataegret.ru
...
Рейтинг: 0 / 0
16.08.2017, 17:15
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39506473
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Maxim Boguk, да, сейчас вполне. Скорее всего надо ещё как-то правильно отконфигурить параметры сервера, на тему использования памяти и прочего.

Ещё есть момент, что в answers для расчетов выражений (по которым строится sql-запрос) есть не только answer_code поле, но и ещё как минимум одно row_code, и ещё пара полей. Чтобы всё это красиво считалось - получается надо море вариантов индексов...

Например если юзер проверяет только answer_code - то нужен индекс по question_number + answer_code. А если проверяет row_code, то надо question_number + row_code, но часто бывает что проверяется одновременно answer_code и row_code, и тут не понятно что надо уже... индекс по question_number + answer_code + row_code? И надо ли туда добавлять interview_id? По ощущению он только портит всё, хотя странно почему... с ним не работает проверка answer_code не на равенство, а на >= и т.д.
...
Рейтинг: 0 / 0
16.08.2017, 17:25
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507090
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Может что-то ещё можно покрутить в настройках? Жутко не стабильные результаты выполнения запросов получаются. Один и тот же запрос может выполняться то более 30 сек., то < 1 сек. Длительное выполнение обычно если какое-то время не трогали эти запросы.

Вот пример:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
2017-08-17 18:33:41.2759 [6] DEBUG Условие счётчика (текущее):  Q905(code >= 2 and code <= 7)  and (Q90202 == 1 or Q90202 == 2 or Q90202 == 6 or Q90202 == 999) 
2017-08-17 18:33:41.2759 [6] DEBUG 
insert into counter_interviews
    (counter_id, version_idx, interview_id)
select
    39962, 9, 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 = 39962 and civ.version_idx = 9 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 = 905) and (answer_code >= 2 and answer_code <= 7)) and
  (exists(select id from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 1)) or
  exists(select id from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 2)) or
  exists(select id from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 6)) or
  exists(select id from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 999))))

2017-08-17 18:34:01.5779 [6] DEBUG Пересчет счетчика успешно завершен: counter_id = 39962, version_idx = 9, затрачено 20307 ms.

Заняло 20 сек. аж, хотя предыдущий запрос свалился по таймауту (30 сек.)
Ещё раз выполняем:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
2017-08-17 18:35:38.4144 [6] DEBUG Условие счётчика (текущее):  Q905(code >= 2 and code <= 7)  and (Q90202 == 1 or Q90202 == 2 or Q90202 == 6 or Q90202 == 999) 
2017-08-17 18:35:38.4144 [6] DEBUG 
insert into counter_interviews
    (counter_id, version_idx, interview_id)
select
    39962, 10, 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 = 39962 and civ.version_idx = 10 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 = 905) and (answer_code >= 2 and answer_code <= 7)) and
  (exists(select id from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 1)) or
  exists(select id from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 2)) or
  exists(select id from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 6)) or
  exists(select id from answers where (interview_id = iv.id and question_number = 90202) and (answer_code = 999))))

2017-08-17 18:35:38.6961 [6] DEBUG Пересчет счетчика успешно завершен: counter_id = 39962, version_idx = 10, затрачено 273 ms.

Вот текущий план из этого селекта:

Код: 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.
"Nested Loop  (cost=1950.95..2179.74 rows=1 width=8) (actual time=489.424..489.424 rows=0 loops=1)"
"  ->  Hash Left Join  (cost=1950.52..2179.26 rows=1 width=16) (actual time=45.265..488.701 rows=50 loops=1)"
"        Hash Cond: (iv.id = civ.interview_id)"
"        Filter: (civ.id IS NULL)"
"        ->  Nested Loop  (cost=1943.28..2149.47 rows=1640 width=16) (actual time=45.215..488.580 rows=50 loops=1)"
"              ->  HashAggregate  (cost=1942.85..1943.00 rows=15 width=8) (actual time=42.399..46.960 rows=7205 loops=1)"
"                    Group Key: answers.interview_id"
"                    ->  Index Scan using ix_answers_questionnumber_answercode on answers  (cost=0.56..1938.48 rows=1749 width=8) (actual time=0.063..33.660 rows=7375 loops=1)"
"                          Index Cond: ((question_number = 905) AND (answer_code >= 2) AND (answer_code <= 7))"
"              ->  Index Scan using pk_interviews on interviews iv  (cost=0.43..13.75 rows=1 width=16) (actual time=0.060..0.060 rows=0 loops=7205)"
"                    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.010..0.010 rows=0 loops=7205)"
"                            Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 1))"
"                            Heap Fetches: 0"
"                    SubPlan 2"
"                      ->  Index Scan using ix_answers_questionnumber_answercode on answers answers_2  (cost=0.56..1028.52 rows=928 width=8) (never executed)"
"                            Index Cond: ((question_number = 90202) 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.002..0.002 rows=0 loops=7205)"
"                            Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 2))"
"                            Heap Fetches: 50"
"                    SubPlan 4"
"                      ->  Index Scan using ix_answers_questionnumber_answercode on answers answers_4  (cost=0.56..892.86 rows=805 width=8) (never executed)"
"                            Index Cond: ((question_number = 90202) 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.002..0.002 rows=0 loops=7155)"
"                            Index Cond: ((interview_id = iv.id) AND (question_number = 90202) AND (answer_code = 6))"
"                            Heap Fetches: 0"
"                    SubPlan 6"
"                      ->  Index Scan using ix_answers_questionnumber_answercode on answers answers_6  (cost=0.56..93.21 rows=83 width=8) (never executed)"
"                            Index Cond: ((question_number = 90202) AND (answer_code = 6))"
"                    SubPlan 7"
"                      ->  Index Scan using ix_answers_questionnumber_answercode on answers answers_7  (cost=0.56..2.79 rows=1 width=0) (actual time=0.032..0.032 rows=0 loops=7155)"
"                            Index Cond: ((question_number = 90202) AND (answer_code = 999))"
"                            Filter: (interview_id = iv.id)"
"                            Rows Removed by Filter: 88"
"                    SubPlan 8"
"                      ->  Index Scan using ix_answers_questionnumber_answercode on answers answers_8  (cost=0.56..2.78 rows=1 width=8) (never executed)"
"                            Index Cond: ((question_number = 90202) AND (answer_code = 999))"
"        ->  Hash  (cost=7.12..7.12 rows=9 width=16) (actual time=0.031..0.031 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..7.12 rows=9 width=16) (actual time=0.030..0.030 rows=0 loops=1)"
"                    Index Cond: ((counter_id = 39962) AND (version_idx = 10))"
"  ->  Index Scan using pk_respondents on respondents r  (cost=0.43..0.47 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=50)"
"        Index Cond: (id = iv.respondent_id)"
"        Filter: (project_id = 994)"
"        Rows Removed by Filter: 1"
"Planning time: 1.743 ms"
"Execution time: 489.610 ms"
...
Рейтинг: 0 / 0
17.08.2017, 18:40
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507104
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Alexey Trizno,

Когда сильно скорость плавает для одного и того же запроса это признак того что данные не в памяти а читаются с дисков что в 10000 и более раз медленнее.
При этом какая часть данных в памяти сейчас а какая на дисках - никто не знает.
Попробуйте включть track_io_timing в конфигe базы
и делать explain (analyze, costs, buffers, timing) вместо explain analyze.

Еще полезно отрестартовать попробовать базу и сразу после рестарта сделать explain (analyze, costs, buffers, timing) для проблемного запроса причем повторить его раза 3 чтобы видеть эффекты от данных в памяти или на дисках.

Ну и конечно мониторинг дисковой нагрузки.

--
Maxim Boguk
dataegret.ru
...
Рейтинг: 0 / 0
17.08.2017, 18:58
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507116
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Также решил сравнить выполнение такого же запроса на старой базе (ms sql 2012, база более 160Gb, машина в два раза слабее чем та, на которой работает pgsql, при этом эта же машина ещё и веб-сервер). В итоге запрос отрабатывает максимум за 140ms, а обычно за <100 ms. Вот запрос на mssql:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select
    495593, 123, iv.id
from
  interviews iv
  inner join respondents r on (r.id = iv.respondentid)
  left outer join countertointerviews civ on (civ.counterid = 495593 and civ.versionidx = 123 and civ.interviewid = iv.id)
where
  r.projectid = 12178 and civ.id is null and 
  exists(select id from answers where (interviewid = iv.id and questionnumber = 999) and (answercode = 9))



Вот план по нему:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select 495593 , 123 , iv . id from interviews iv inner join respondents r on ( r . id = iv . respondentid ) left outer join countertointerviews civ on ( civ . counterid = @0 and civ . versionidx = @1 and civ . interviewid = iv . id ) where r . projectid = @2 and civ . id is null and exists ( select id from answers where ( interviewid = iv . id and questionnumber = @3 ) and ( answercode = @4 ) )
  |--Compute Scalar(DEFINE:([Expr1009]=(495593), [Expr1010]=(123)))
       |--Filter(WHERE:([TestDB].[dbo].[CounterToInterviews].[ID] as [civ].[ID] IS NULL))
            |--Nested Loops(Left Outer Join, WHERE:([TestDB].[dbo].[CounterToInterviews].[InterviewID] as [civ].[InterviewID]=[TestDB].[dbo].[Interviews].[ID] as [iv].[ID]))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([r].[ID]))
                 |    |--Index Seek(OBJECT:([TestDB].[dbo].[Respondents].[IX_Respondents_1] AS [r]), SEEK:([r].[ProjectID]=[@2]) ORDERED FORWARD)
                 |    |--Stream Aggregate(GROUP BY:([iv].[ID]))
                 |         |--Nested Loops(Inner Join, OUTER REFERENCES:([iv].[ID]))
                 |              |--Index Seek(OBJECT:([TestDB].[dbo].[Interviews].[IX_Interviews_1] AS [iv]), SEEK:([iv].[RespondentID]=[TestDB].[dbo].[Respondents].[ID] as [r].[ID]) ORDERED FORWARD)
                 |              |--Index Seek(OBJECT:([TestDB].[dbo].[Answers].[IX_Answers_3]), SEEK:([TestDB].[dbo].[Answers].[InterviewID]=[TestDB].[dbo].[Interviews].[ID] as [iv].[ID] AND [TestDB].[dbo].[Answers].[QuestionNumber]=[@3] AND [TestDB].[dbo].[Answers].[AnswerCode]=[@4]) ORDERED FORWARD)
                 |--Index Seek(OBJECT:([TestDB].[dbo].[CounterToInterviews].[IX_CounterToInterviews_1] AS [civ]), SEEK:([civ].[CounterID]=[@0] AND [civ].[VersionIdx]=[@1]) ORDERED FORWARD)

И подобный же запрос на pgsql (подобрал чтобы похожее было кол-во итоговых строк в выборке):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
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.117..1507.838 rows=817 loops=1)"
"  ->  Hash Left Join  (cost=4058.88..4149.45 rows=1 width=16) (actual time=228.576..1023.178 rows=90146 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.540..920.625 rows=90146 loops=1)"
"              ->  HashAggregate  (cost=4055.65..4055.95 rows=30 width=8) (actual time=228.519..284.902 rows=90146 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.025..137.810 rows=90146 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=90146)"
"                    Index Cond: (id = answers.interview_id)"
"        ->  Hash  (cost=2.78..2.78 rows=1 width=16) (actual time=0.016..0.016 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.015..0.015 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=90146)"
"        Index Cond: (id = iv.respondent_id)"
"        Filter: (project_id = 994)"
"        Rows Removed by Filter: 1"
"Planning time: 0.589 ms"
"Execution time: 1508.336 ms"

Разница примерно в 10 раз по скорости, причём железо реально заметно получше.
Причём, постоянное выполнение одного и того же запроса уменьшает время выполнения до 660ms минимум, меньше совсем никак.

Беда какая-то совсем. Этот запрос простейший, обычно там exist-ов по 5-10 штук, с различными условиями.
Как быть? Что крутить? Спасите помогите (с)

Как я понимаю, mssql никогда не будет в этом случае рассматривать всю таблицу answers, а будет брать только то, что касается interviews внутри искомого project_id (из respondents). А в pgsql почему-то получается что сначала выбираем всё подходящее из answers (она может быть бесконечно большой), а потом отбираем от этого огромного кол-ва записей только подходящие по interview_id... как-то это не правильно. При таком подходе скорость выполнения запроса зависит от кол-ва записей в answers (и других таблицах), а не кол-ва записей для конкретного одного проекта, что рушит всю идею совсем. Возможно надо как-то иначе совсем переписать запрос, чтобы не было зависимости от всех записей answers? В mssql базе этой в answers сейчас 185954703 записей и постоянно добавляется, при этом скорость выполнения запросов никак от этого не зависит.
...
Рейтинг: 0 / 0
17.08.2017, 19:06
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507121
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Пока мысль только одна - добавить в answers поле project_id, что избавит от джойна в respondents, и, возможно, как-то поможет.
Но это же не наши методы, Карл! Это же избыточность и прочее. Тем более не всегда выборка идет просто для одного project_id, могут ещё участвовать поля из respondents для фильтрации нужных. Все эти поля в одну таблицу не перенести.
...
Рейтинг: 0 / 0
17.08.2017, 19:09
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507133
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Alexey Trizno,

1)Давайте сравним
что дает select count(*) from respondents r where project_id = 994;
на postgresql и на mssql
причем на postgresql приведите результат expain analyze.

2)Я вижу что mssql дает почти тот же план. И он тоже начинается с выбора из answers (а не как вы описали от выбора из respondends).

Для теста сделайте в конфиге базы default_statistics_target=10000
и сделайте analyze всей базы. Может поможет.


PS: частично проблема postgres в 30 кратной ошибке в оценке
" -> Index Scan using ix_answers_questionnumber_answercode on answers (cost=0.56..4046.48 rows=3668 width=8) (actual time=0.025..137.810 rows=90146 loops=1)"
" Index Cond: ((question_number = 999) AND (answer_code = 9))"
откуда и выбирается неудачный план. Повышение default_statistics_target может этот вопрос решить (а может и нет).

PPS: если надо могу подсказать как более менее наверняка пришпилить тот план что вы хотите если ничего другого не поможет.
...
Рейтинг: 0 / 0
17.08.2017, 19:20
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507140
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Maxim Boguk, там совсем нет никакой нагрузки на диск :( вообще. Результат же всего 800 записей, там вообще перебирается их минимум для такой мощности системы. Вот пример статистики (выделен справа кусок, когда постоянно выполняю тестовый запрос, который ровно выполняется за ~800ms):
...
Рейтинг: 0 / 0
17.08.2017, 19:28
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507144
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Alexey Trizno,

800ms - максимальная скорость которая получается при выбранном плане.
Как попробовать изменить план - я написал (поднять default_statistics_target и сделать analyze базы).
Если не поможет - есть варианты как переписать запрос по другому.
...
Рейтинг: 0 / 0
17.08.2017, 19:32
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507151
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Alexey Trizno,

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

--
Maxim Boguk
dataegret.ru
...
Рейтинг: 0 / 0
17.08.2017, 20:27
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507183
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
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"



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

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

на этом проекте в mssql запрос тестовый выполняется примерно 400...500ms, что конечно близко к pgsql, хоть там и всё равно медленнее (можно предположить другую структуру записей, т.к. всё же разные проекты).
но первое выполнение идёт жутко...? Ы
...
Рейтинг: 0 / 0
17.08.2017, 20:46
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507185
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
qwwq, в смысле что всё в памяти и так?
...
Рейтинг: 0 / 0
17.08.2017, 20:54
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507193
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
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.
"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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
На счет переписывания запроса - попробуйте вот так вот:


Код: 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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Maxim Boguk, точнее нет - без explain и analyze запрос выполняется за 60мс.
...
Рейтинг: 0 / 0
18.08.2017, 12:12
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507473
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
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
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
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
1.
( question_number, answer_code,interview_id);


на предмет лучших ожыданий по попаданию на уже прочитанное.
...
Рейтинг: 0 / 0
18.08.2017, 17:03
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507703
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
qwwq, пока сделали замер скорости дисков, вышло не очень радужно: линейное чтение 170Mb/s, а рандомное чуть не в 200 раз медленнее. Но на машине 32Gb памяти, туда по идее должно влезть бОльшая часть основных данных, а уж рассматриваемые 4 таблицы вообще целиком вместе с индексами несколько раз. Зачем вообще на диск лезть для чтения?
...
Рейтинг: 0 / 0
18.08.2017, 18:25
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507717
Sergei.Agalakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergei.Agalakov
Участник
Потому что читается только то, что запрашивается. Даже если вы 'разогреете' данные сделав full scan на всех таблицах так что они пойдут в память, то и тогда эти данные могут со временем быть выгружены из кэша. Постгрес довольно трудно будет заставить закэшировать все таблицы и индексы до первого рабочего вызова.
...
Рейтинг: 0 / 0
18.08.2017, 19:15
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507758
Alexey Trizno
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexey Trizno
Гость
Sergei.Agalakov, пока решили добавить в сервер ssd диск и перенести базу на него. Посмотрим что получится. Ну и все эти запросы теперь строятся с WITH для уменьшения выборки, что в 2-4 раза улучшает результат.
...
Рейтинг: 0 / 0
18.08.2017, 22:31
| Ответить | Цитировать | Написать  
Помогите перенести запрос с mssql на postgresql
    #39507761
PgSQLanonymous3
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PgSQLanonymous3
Участник
Sergei.AgalakovПостгрес довольно трудно будет заставить закэшировать все таблицы и индексы до первого рабочего вызова.
Можно попробовать https://www.postgresql.org/docs/current/static/pgprewarm.html
...
Рейтинг: 0 / 0
18.08.2017, 23:20
| Ответить | Цитировать | Написать  
52 сообщений из 52, показаны все 3 страниц
  все  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите перенести запрос с mssql на postgresql
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=53&tid=1996284]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 35ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 80ms
get tp. blocked users:
 1ms
others: 321ms
total:  478ms
созд. / загр.: 478ms
x
x
Закрыть


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