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

ReSQL.ru

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

Гость

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

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

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

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

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

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

все

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

#39505175

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

Ролг Хупин

Участник

Откуда: Чебаркуль

Сообщения: 4 817

Рейтинг: 0 / 0

"Чего в принципе никогда делать не будет mssql."

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

Структура таблиц и индексов?

...

Рейтинг:

0 / 0

14.08.2017, 18:22

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

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

#39505237

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

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

Гость

Ниже структура таблиц. Поля не имеющие отношения к вопросу не убирал, а то вдруг...:

Код: 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

Гость

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

Гость

Кол-во строк в таблицах на данный момент:

answers: 37 687 200
interviews: 5 799 250
respondents: 4 578 730

...

Рейтинг:

0 / 0

15.08.2017, 12:18

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

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

#39505565

p2.

Участник

Сообщения: 495

Рейтинг: 0 / 0

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

Участник

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

Рейтинг: 0 / 0

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

Участник

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

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

Рейтинг: 0 / 0

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

Участник

Сообщения: 749

Рейтинг: 0 / 0

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

Гость

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

Гость

p2., переписывание exist'ов или какая-то их оптимизация в этом конкретном случае - особо не поможет, т.к. как я написал - этот запрос генерируется по произвольному выражению и юзер может написать что угодно, используя AND/OR/NOT, скобки и любые логические операции.

...

Рейтинг:

0 / 0

15.08.2017, 16:39

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

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

#39505821

Alexey Trizno

Гость

Alexius, диски на сервере не ssd, но там совсем не видно никакой активной работы с дисками. Тишина. Да и не такая большая база, вообще всё в кэши могло бы влезть. Вывод запросов ниже:

Код: plaintext

\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

Участник

Сообщения: 749

Рейтинг: 0 / 0

Alexey Trizno,

можно еще результат запроса

Код: sql

select count(distinct interview_id) from answers;

...

Рейтинг:

0 / 0

15.08.2017, 17:08

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

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

#39505852

Alexey Trizno

Гость

Alexius, результат = 3758019

...

Рейтинг:

0 / 0

15.08.2017, 17:22

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

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

#39505860

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

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

Гость

Alexius, спасибо, попробую. Результаты напишу.

...

Рейтинг:

0 / 0

15.08.2017, 17:54

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

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

#39506379

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

Участник

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

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

Рейтинг: 0 / 0

Alexey Trizno,

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

--
Maxim Boguk
dataegret.ru

...

Рейтинг:

0 / 0

16.08.2017, 17:15

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

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

#39506473

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

Гость

Может что-то ещё можно покрутить в настройках? Жутко не стабильные результаты выполнения запросов получаются. Один и тот же запрос может выполняться то более 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

Участник

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

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

Рейтинг: 0 / 0

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

Гость

Также решил сравнить выполнение такого же запроса на старой базе (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

Гость

Пока мысль только одна - добавить в answers поле project_id, что избавит от джойна в respondents, и, возможно, как-то поможет.
Но это же не наши методы, Карл! Это же избыточность и прочее. Тем более не всегда выборка идет просто для одного project_id, могут ещё участвовать поля из respondents для фильтрации нужных. Все эти поля в одну таблицу не перенести.

...

Рейтинг:

0 / 0

17.08.2017, 19:09

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

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

#39507133

Maxim Boguk

Участник

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

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

Рейтинг: 0 / 0

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

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

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

все

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?fid=53&fpage=68&tid=1996284]:	0ms
get settings:	9ms
get forum list:	13ms
check forum access:	3ms
check topic access:	3ms
track hit:	33ms
get topic data:	12ms
get forum data:	3ms
get page messages:	63ms
get tp. blocked users:	1ms
others:	16ms

total:	156ms