PostgreSql pivot table from two tables - performance

Question

I have question about performance:

I have table Employees

id	name
1	name surname 1
2	name surname 2

And table plan

emp_id	shift_date	shift_begin	shift_end
1	2022-01-01	08:00	17:00
1	2022-01-02	08:00	17:00
1	2022-01-04	08:00	17:00
2	2022-01-01	08:00	17:00
2	2022-01-02	08:00	17:00
2	2022-01-03	08:00	17:00

Note: emp_id is a id of employee

and I have following query:

SELECT id,name,
(select concat_ws('-',to_char(shift_begin ,'HH24:MI'),to_char(shift_end ,'HH24:MI'),typ) from plan where plan.emp_id = employee.id and shift_date =  '2022-01-01') as d0,
(select concat_ws('-',to_char(shift_begin ,'HH24:MI'),to_char(shift_end ,'HH24:MI'),typ) from plan where plan.emp_id = employee.id and shift_date = '2022-01-02') as d1,
(select concat_ws('-',to_char(shift_begin ,'HH24:MI'),to_char(shift_end ,'HH24:MI'),typ) from plan where plan.emp_id = employee.id and shift_date = '2022-01-03') as d2,
(select concat_ws('-',to_char(shift_begin ,'HH24:MI'),to_char(shift_end ,'HH24:MI'),typ) from plan where plan.emp_id = employee.id and shift_date = '2022-01-04') as d3,
(select concat_ws('-',to_char(shift_begin ,'HH24:MI'),to_char(shift_end ,'HH24:MI'),typ) from plan where plan.emp_id = employee.id and shift_date = '2022-01-05') as d4
-- continues to end of month
from employee;

Result is pretty good...

id	name	d0	d1	d2	d3	d4	---> d30
1	name surname 1	08:00-17:00	08:00-17:00		08:00-17:00		---> d30
2	name surname 2	08:00-17:00	08:00-17:00	08:00-17:00			---> d30

...but when I have for example 50 employees which I would display in table (50 emp * 31 days) it drops in performance (after insert, delete)...

I have a crosstab too but it doesn't show me any results

Now back to the topic: Its a good option ? or use crosstab ? (Fyi in crosstab I have selection from table plan, but I want to select each of table employee).

I appreciate any help.

Select *once* from the table use use a *case expression* – Stu Feb 03 '22 at 22:57 — Stu, Feb 03 '22 at 22:57
do you mean contidional aggregation ? – Jorns 90 Feb 03 '22 at 22:57 — Jorns 90, Feb 03 '22 at 22:57
Yes that's correct, *conditional* using a case expression. – Stu Feb 03 '22 at 23:03 — Stu, Feb 03 '22 at 23:03

LukStorms · Answer 1 · 2022-02-04T00:56:56.800

0

An alternative method to pivot that works in most databases is conditional aggregation.

SELECT emp.id, emp.name
, STRING_AGG(CASE EXTRACT(DAY FROM plan.shift_date) WHEN 1 THEN concat_ws('-',to_char(plan.shift_begin,'HH24:MI'),to_char(plan.shift_end,'HH24:MI'),typ) END, ';') AS d1
, STRING_AGG(CASE EXTRACT(DAY FROM plan.shift_date) WHEN 2 THEN concat_ws('-',to_char(plan.shift_begin,'HH24:MI'),to_char(plan.shift_end,'HH24:MI'),typ) END, ';') AS d2
, STRING_AGG(CASE EXTRACT(DAY FROM plan.shift_date) WHEN 3 THEN concat_ws('-',to_char(plan.shift_begin,'HH24:MI'),to_char(plan.shift_end,'HH24:MI'),typ) END, ';') AS d3
-- continues to end of month
FROM employee emp
LEFT JOIN plan 
  ON plan.emp_id = emp.id
 AND plan.shift_date >= '2022-01-01' 
 AND plan.shift_date  < '2022-02-01' 
GROUP BY emp.id, emp.name
ORDER BY emp.id, emp.name;

If it's certain that they only have 1 shift per day.
Then MAX will do just fine.

, MAX(CASE EXTRACT(DAY FROM plan.shift_date) WHEN 31 THEN concat_ws('-',to_char(plan.shift_begin,'HH24:MI'),to_char(plan.shift_end,'HH24:MI'),typ) ELSE '' END) AS d31

edited Feb 04 '22 at 00:56

answered Feb 03 '22 at 23:01

LukStorms

28,916
5
31
45

but "shift_date" is from another table how to achieve this? – Jorns 90 Feb 03 '22 at 23:04
Had to add the join to plan – LukStorms Feb 03 '22 at 23:07
Problem solved for now thanks for help. – Jorns 90 Feb 03 '22 at 23:08
Check update, by comparing the day of the month it'll be easy to change month by changing the `WHERE` criteria. – LukStorms Feb 03 '22 at 23:15
Okay thanks i look on this, but there is little problem, if some month have a 28 days like february or 30 ? – Jorns 90 Feb 03 '22 at 23:18
Currently d31 would be NULL then. Unless you put an `ELSE '' ` in the `CASE WHEN` – LukStorms Feb 03 '22 at 23:20
Perfect, thanks... – Jorns 90 Feb 03 '22 at 23:24
Btw, I changed MAX to STRING_AGG. Just in case some have more than 1 shift per day. – LukStorms Feb 04 '22 at 00:13
It is treated to always 1 shift for each employee / 1 day, currently i have used cond. aggregation MAX() with case then else and its work fast. – Jorns 90 Feb 04 '22 at 00:38

Erwin Brandstetter · Answer 2 · 2022-02-03T23:54:24.163

For lots of result columns, crosstab() is typically shortest and fastest:

SELECT *
FROM   crosstab(
   $$
   SELECT p.emp_id, e.name, p.shift_date
        , concat_ws('-', to_char(p.shift_begin, 'HH24:MI'), to_char(p.shift_end,'HH24:MI'))
   FROM   employees e
   LEFT   JOIN plan p ON p.emp_id = e.id
                     AND p.shift_date >= '2022-01-01' 
                     AND p.shift_date <= '2022-01-31' 
   ORDER  BY e.id, p.shift_date;
   $$
 , $$SELECT generate_series (timestamp '2022-01-01'
                           , timestamp '2022-01-31'
                           , interval '1 day')::date$$
   ) AS ct (
      id int, name text
    , d1  text, d2  text, d3  text, d4  text, d5  text, d6  text, d7  text, d8  text, d9  text, d10 text
    , d11 text, d12 text, d13 text, d14 text, d15 text, d16 text, d17 text, d18 text, d19 text, d20 text
    , d21 text, d22 text, d23 text, d24 text, d25 text, d26 text, d27 text, d28 text, d29 text, d30 text
    , d31 text);

db<>fiddle here

See:

PostgreSQL Crosstab Query

Why generate_series (timestamp '2022-01-01', ...? See:

Generating time series between two dates in PostgreSQL

I would generate above query dynamically for any given date range. Related examples:

score 0 · Answer 3 · answered Feb 04 '22 at 07:27

Another option is to aggregate the shift per employee before doing the "pivot", e.g. into a JSON value:

SELECT emp.id, 
       emp.name,
       shifts ->> '1' as d1,
       shifts ->> '2' as d2,
       shifts ->> '3' as d3,
       shifts ->> '4' as d4,
       shifts ->> '5' as d5,
       shifts ->> '6' as d6,
       shifts ->> '7' as d7,
       shifts ->> '8' as d8,
       shifts ->> '9' as d9,
       ... 
from employees emp
  left join (
    select emp_id, 
           jsonb_object_agg(extract(day from shift_date),
                            concat_ws('-', to_char(shift_begin, 'hh24:mi'), to_char(shift_end, 'hh24:mi'))) as shifts
    from plan p
     where p.shift_date >= '2022-01-01' 
       and p.shift_date  < '2022-02-01' 
    group by emp_id
  ) p on p.emp_id = emp.id
order by emp.id;
;

Depending on how you use the result, you might not even need to extract each day into a separate column if you can use the JSON value directly in your frontend.

PostgreSql pivot table from two tables - performance

3 Answers3