Postgres 11

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

create table employee as
  select 1 as id, 'Bob' as name, 1000000 as salary, 'IT' as department union all
  select 2, 'John', 100000, 'IT' union all
  select 3, 'Tom', 10000, 'IT' union all
  select 4, 'Ann', 2000000, 'HR' union all
  select 5, 'Tony', 200000, 'HR' union all
  select 6, 'Mary', 20000, 'HR'
6 rows affected

select *
  from employee;


id
name
salary
department


1
Bob
1000000
IT

2
John
100000
IT

3
Tom
10000
IT

4
Ann
2000000
HR

5
Tony
200000
HR

6
Mary
20000
HR


SELECT department, COUNT(*), SUM(SALARY), AVG(SALARY)
  FROM employee
  GROUP BY department;


department
count
sum
avg


IT
3
1110000
370000.000000000000

HR
3
2220000
740000.000000000000

id	name	salary	department
1	Bob	1000000	IT
2	John	100000	IT
3	Tom	10000	IT
4	Ann	2000000	HR
5	Tony	200000	HR
6	Mary	20000	HR