Postgres 13

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 tablename (depname VARCHAR(9), empno INTEGER, salary INTEGER, avg FLOAT);

INSERT INTO tablename (depname, empno, salary, avg) VALUES
  ('develop', '11', '5200', '5020.0000000000000000'),
  ('develop', '7', '4200', '5020.0000000000000000'),
  ('personnel', '5', '3500', '3700.0000000000000000'),
  ('personnel', '2', '3900', '3700.0000000000000000'),
  ('sales', '3', '4800', '4866.6666666666666667'),
  ('sales', '1', '5000', '4866.6666666666666667'),
  ('sales', '4', '4800', '4866.6666666666666667'),
  ('tech', '9', '4500', '5020.0000000000000000'),
  ('tech', '8', '6000', '5020.0000000000000000'),
  ('tech', '10', '5200', '5020.0000000000000000');

CREATE TABLE



INSERT 0 10


WITH cte AS (
  SELECT *, DENSE_RANK() OVER (ORDER BY depname) dr
  FROM tablename
)
SELECT * FROM cte;


depname
empno
salary
avg
dr


develop
11
5200
5020
1

develop
7
4200
5020
1

personnel
5
3500
3700
2

personnel
2
3900
3700
2

sales
3
4800
4866.666666666667
3

sales
1
5000
4866.666666666667
3

sales
4
4800
4866.666666666667
3

tech
9
4500
5020
4

tech
8
6000
5020
4

tech
10
5200
5020
4



SELECT 10


WITH cte AS (
  SELECT *, DENSE_RANK() OVER (ORDER BY depname) dr
  FROM tablename
)
SELECT depname, empno, salary, avg
FROM cte
WHERE dr <= 2;


depname
empno
salary
avg


develop
11
5200
5020

develop
7
4200
5020

personnel
5
3500
3700

personnel
2
3900
3700



SELECT 4


WITH cte AS (
  SELECT *, DENSE_RANK() OVER (ORDER BY depname) dr
  FROM tablename
)
SELECT depname, empno, salary, avg
FROM cte
WHERE dr > 1 AND dr <= 3;


depname
empno
salary
avg


personnel
5
3500
3700

personnel
2
3900
3700

sales
3
4800
4866.666666666667

sales
1
5000
4866.666666666667

sales
4
4800
4866.666666666667



SELECT 5

depname	empno	salary	avg	dr
develop	11	5200	5020	1
develop	7	4200	5020	1
personnel	5	3500	3700	2
personnel	2	3900	3700	2
sales	3	4800	4866.666666666667	3
sales	1	5000	4866.666666666667	3
sales	4	4800	4866.666666666667	3
tech	9	4500	5020	4
tech	8	6000	5020	4
tech	10	5200	5020	4