agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: postgres list <pgsql-sql@postgresql.org>
Subject: summary view design / performance
Date: Wed, 18 Apr 2018 10:20:39 +0100
Message-ID: <201804181020.39745.gary.stainburn@ringways.co.uk> (raw)
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.
select * from service_job_log_summary where sj_id in (123,124,145..........)
view thread (2+ messages) latest in thread
Message-ID: <201804181020.39745.gary.stainburn@ringways.co.uk>
Permalink: ../201804181020.39745.gary.stainburn@ringways.co.uk/
Also on: postgresql.org/message-id/201804181020.39745.gary.stainburn@ringways.co.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: summary view design / performance
In-Reply-To: <201804181020.39745.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox