Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select setseed(.42);

create table employee_department (
  effective_department_from_date,
  effective_department_end_date,
  department_id
  ) as
select date_hired
      ,date_hired+random()*'7 years'::interval
      ,(random()*12)::int
from generate_series(1,2e4)g,
  lateral(select g,now()
                   -'6 years'::interval
                   -random()*'5 years'::interval
                   as date_hired);

--inspect samples
select * from employee_department
  tablesample bernoulli(.42)repeatable(.42)
  limit 10;

create table departments(
  department_id,
  department) as 
select g,'department_'||g
from generate_series(0,13)g;

--inspect samples
select * from departments;

alter table departments add primary key (department_id);

create index on employee_department (department_id);


setseed






SELECT 1



SELECT 20000




effective_department_from_date
effective_department_end_date
department_id


2015-06-30 14:24:09.515934+00
2016-08-07 17:01:02.968734+00
8

2017-06-17 10:54:49.634334+00
2022-05-04 11:05:22.859934+00
11

2015-11-22 01:32:02.527134+00
2017-10-28 15:09:16.341534+00
3

2014-08-22 07:19:09.247134+00
2015-07-15 09:54:49.173534+00
3

2016-02-21 22:44:08.200734+00
2022-08-22 00:22:45.391134+00
10

2013-05-31 17:14:15.170334+00
2016-04-05 23:25:41.359134+00
6

2015-05-12 23:48:17.925534+00
2020-04-02 22:24:07.154334+00
9

2017-01-19 05:43:46.975134+00
2022-06-15 06:50:29.023134+00
0

2014-01-02 16:17:43.624734+00
2019-12-12 07:18:19.307934+00
3

2014-01-09 15:11:03.650334+00
2014-07-11 05:29:01.720734+00
4



SELECT 10



SELECT 14




department_id
department


0
department_0

1
department_1

2
department_2

3
department_3

4
department_4

5
department_5

6
department_6

7
department_7

8
department_8

9
department_9

10
department_10

11
department_11

12
department_12

13
department_13



SELECT 14



ALTER TABLE



CREATE INDEX


vacuum analyze employee_department;

VACUUM


WITH hiring_count AS (
    SELECT EXTRACT(YEAR FROM effective_department_from_date) AS year,
           department,  
           COUNT(*) AS total_hiring
    FROM employee_department
    JOIN departments
        ON employee_department.department_id = departments.department_id
    GROUP BY year, department
), departure_count AS (
    SELECT EXTRACT(YEAR FROM effective_department_end_date) AS year, 
           department,
           COUNT(*) AS total_departure
    FROM employee_department
    JOIN departments
        ON employee_department.department_id = departments.department_id
    GROUP BY year, department
)
SELECT hiring_count.year, 
       hiring_count.department,
       total_hiring, 
       total_departure
FROM hiring_count
JOIN departure_count
    using (year_departed,department_id)
order by year,department 
  limit 10;

ERROR:  column "year_departed" specified in USING clause does not exist in left table


WITH hiring_count AS materialized(
    SELECT EXTRACT(YEAR FROM effective_department_from_date) AS year_hired,
           EXTRACT(YEAR FROM effective_department_end_date) AS year_departed,
           department,  
           COUNT(*) AS total_hiring
    FROM employee_department
    JOIN departments
        ON employee_department.department_id = departments.department_id
    GROUP BY grouping sets ( (year_hired, department)
                            ,(year_departed, department)) )
SELECT *     
FROM hiring_count
order by 1,2,3
limit 10;


year_hired
year_departed
department
total_hiring


2013
null
department_0
119

2013
null
department_1
276

2013
null
department_10
262

2013
null
department_11
260

2013
null
department_12
145

2013
null
department_2
242

2013
null
department_3
255

2013
null
department_4
277

2013
null
department_5
258

2013
null
department_6
240



SELECT 10


WITH hiring_count AS (
    SELECT EXTRACT(YEAR FROM effective_department_from_date) AS year_hired,
           EXTRACT(YEAR FROM effective_department_end_date) AS year_departed,
           department,
           d.department_id,
           COUNT(*) AS total
    FROM employee_department ed
    JOIN departments d
        using (department_id)
    GROUP BY grouping sets ( (year_hired, d.department_id)
                            ,(year_departed, d.department_id)) )
SELECT coalesce( a.year_hired
                ,b.year_departed) as year
      ,a.department
      ,a.total as total_hiring
      ,b.total as total_departure
FROM hiring_count a join hiring_count b
  using (year_departed,department_id)
order by year,a.department_id
limit 10;


year
department
total_hiring
total_departure


2013
department_0
4
4

2013
department_1
9
9

2013
department_2
17
17

2013
department_3
19
19

2013
department_4
11
11

2013
department_5
17
17

2013
department_6
18
18

2013
department_7
11
11

2013
department_8
17
17

2013
department_9
15
15



SELECT 10

effective_department_from_date	effective_department_end_date	department_id
2015-06-30 14:24:09.515934+00	2016-08-07 17:01:02.968734+00	8
2017-06-17 10:54:49.634334+00	2022-05-04 11:05:22.859934+00	11
2015-11-22 01:32:02.527134+00	2017-10-28 15:09:16.341534+00	3
2014-08-22 07:19:09.247134+00	2015-07-15 09:54:49.173534+00	3
2016-02-21 22:44:08.200734+00	2022-08-22 00:22:45.391134+00	10
2013-05-31 17:14:15.170334+00	2016-04-05 23:25:41.359134+00	6
2015-05-12 23:48:17.925534+00	2020-04-02 22:24:07.154334+00	9
2017-01-19 05:43:46.975134+00	2022-06-15 06:50:29.023134+00	0
2014-01-02 16:17:43.624734+00	2019-12-12 07:18:19.307934+00	3
2014-01-09 15:11:03.650334+00	2014-07-11 05:29:01.720734+00	4

department_id	department
0	department_0
1	department_1
2	department_2
3	department_3
4	department_4
5	department_5
6	department_6
7	department_7
8	department_8
9	department_9
10	department_10
11	department_11
12	department_12
13	department_13

year_hired	year_departed	department	total_hiring
2013	null	department_0	119
2013	null	department_1	276
2013	null	department_10	262
2013	null	department_11	260
2013	null	department_12	145
2013	null	department_2	242
2013	null	department_3	255
2013	null	department_4	277
2013	null	department_5	258
2013	null	department_6	240