Re: summary view design / performance
От | Achilleas Mantzios |
---|---|
Тема | Re: summary view design / performance |
Дата | |
Msg-id | 05244155-e345-b76a-6c0c-1979c9122a9f@matrix.gatewaynet.com обсуждение исходный текст |
Ответ на | summary view design / performance (Gary Stainburn <gary.stainburn@ringways.co.uk>) |
Список | pgsql-sql |
On 18/04/2018 12:20, Gary Stainburn wrote: > Morning all, > > I'm after people's opinions redarding performance / system load in the > following select / view. Also, if there is a better technique I'd love to > hear it. > > I have the following table, which is an audit and message log for amendments > to the service_jobs table. > > Table "public.service_jobs_log" > Column | Type | > Modifiers > ---------------+-----------------------------+------------------------------------------------------------------- > sj_seq | integer | not null default > nextval('service_jobs_log_sj_seq_seq'::regclass) > sj_id | integer | not null > sj_u_id | integer | not null > sj_text | text | not null > sj_timestamp | timestamp without time zone | default now() > sjl_id | integer | not null default 10 > sjl_answer_to | integer | > Indexes: > "service_jobs_log_pkey" PRIMARY KEY, btree (sj_seq) > "service_jobs_log_sj_id_index" btree (sj_id) > "service_jobs_log_sj_timestamp_index" btree (sj_timestamp) > "service_jobs_log_sjl_id_index" btree (sjl_id) > Foreign-key constraints: > "service_jobs_log_sj_id_fkey" FOREIGN KEY (sj_id) REFERENCES > service_jobs(sj_id) > "service_jobs_log_sj_u_id_fkey" FOREIGN KEY (sj_u_id) REFERENCES > users(u_id) > "service_jobs_log_sjl_answer_to_fkey" FOREIGN KEY (sjl_answer_to) > REFERENCES service_jobs_log(sj_seq) > "service_jobs_log_sjl_id_fkey" FOREIGN KEY (sjl_id) REFERENCES > service_jobs_log_types(sjl_id) > > goole=# select * from service_jobs_log_types order by sjl_id; > sjl_id | sjl_desc | sjl_show_chat | sjl_colour > --------+----------+---------------+------------ > 10 | Activity | f | > 20 | Comment | f | > 30 | Question | f | > 40 | Answer | f | > (4 rows) > > > The view I want is: > > j_id > count > comment_count > highest_comment_seq > question_count > highest_question_seq > answer_count > highest_answer_seq > > I have two solutions. Firstly having multiple sub-selects > > select sjl.sj_id, sjl.count, > coalesce(cc.count,0) as comment_count, cc.max as highest_comment_seq, > coalesce(qc.count,0) as question_count, qc.max as highest_question_seq, > coalesce(ac.count,0) as answer_count, ac.max as highest_answer_seq > from (select sj_id, count(sj_id) from service_jobs_log group by sj_id) sjl > left outer join (select sj_id, count(sj_id), max(sj_seq) from > service_jobs_log where sjl_id=20 group by sj_id) cc on sjl.sj_id = cc.sj_id > left outer join (select sj_id, count(sj_id), max(sj_seq) from > service_jobs_log where sjl_id=30 group by sj_id) qc on sjl.sj_id = qc.sj_id > left outer join (select sj_id, count(sj_id), max(sj_seq) from > service_jobs_log where sjl_id=40 group by sj_id) ac on sjl.sj_id = ac.sj_id > ; > Secondly, having one query with lots of case statements > > > select sj_id, count(sj_id), > count(comments) as comment_count, max(comment_seq) as highest_comment_seq, > count(questions) as question_count, max(question_seq) as > highest_question_seq, > count(answers) as answer_count, max(answer_seq) as highest_answer_seq > from ( > select sj_id, > case when sjl_id = 20 then 1 else NULL end as comments, > case when sjl_id = 20 then sj_seq else NULL end as comment_seq, > case when sjl_id = 30 then 1 else NULL end as questions, > case when sjl_id = 30 then sj_seq else NULL end as question_seq, > case when sjl_id = 40 then 1 else NULL end as answers, > case when sjl_id = 40 then sj_seq else NULL end as answer_seq > from service_jobs_log) foo > group by sj_id; > > In the production environment the view will be called with a list of required > sj_id's, e.g. By intuition I'd say the 2nd one looks nicer and easier to maintain, and it does a single scan on service_jobs_log. Have you tried them? how do they perform? > > select * from service_job_log_summary where sj_id in (123,124,145..........) > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
В списке pgsql-sql по дате отправления: