From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: postgres list <pgsql-sql@postgresql.org>
Subject: summary view design / performance
Date: Wed, 18 Apr 2018 10:20:39 +0100
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201804181020.39745.gary.stainburn@ringways.co.uk>
Precedence: bulk

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..........)