Subject: Re: summary view design / performance
To: pgsql-sql@lists.postgresql.org
References: <201804181020.39745.gary.stainburn@ringways.co.uk>
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Message-ID: <05244155-e345-b76a-6c0c-1979c9122a9f@matrix.gatewaynet.com>
Date: Wed, 18 Apr 2018 15:45:16 +0300
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101
 Thunderbird/52.6.0
MIME-Version: 1.0
In-Reply-To: <201804181020.39745.gary.stainburn@ringways.co.uk>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk

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