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

select version();

version

PostgreSQL 11.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit

SELECT 1

CREATE TABLE nam_cit_sal
(
  name   TEXT NOT NULL,
  city   TEXT NOT NULL,
  salary INTEGER NOT NULL
);

CREATE TABLE

INSERT INTO nam_cit_sal
VALUES
('Bill', 'New York',    45000),
('Paul', 'Galway',      35000),
('Bill', 'Yokohama',    35000),
('Bill', 'Chicago',     50000),
('Adam', 'New York',    75000),
('Mike', 'Los Angeles', 67000),
('Paul', 'Paris',       85000),
('Paul', 'Dublin',      55000),
('Paul', 'Munich',      80000);

INSERT 0 9

--
--  Paul 55000 (Galway and Dublin) twice in table, only appears once in 
--  DISTINCT without ON
--

SELECT 
  DISTINCT name, salary
FROM nam_cit_sal;

name
salary

Bill
45000

Paul
85000

Bill
50000

Mike
67000

Paul
55000

Bill
35000

Paul
35000

Paul
80000

Adam
75000

SELECT 9

SELECT DISTINCT ON (name) name, salary
FROM nam_cit_sal
ORDER BY name;

name
salary

Adam
75000

Bill
35000

Mike
67000

Paul
35000

SELECT 4

SELECT DISTINCT ON (name) name, salary
FROM nam_cit_sal
ORDER BY name DESC;

name
salary

Paul
85000

Mike
67000

Bill
45000

Adam
75000

SELECT 4

SELECT DISTINCT ON (name) name, city
FROM nam_cit_sal
ORDER BY name, city;

name
city

Adam
New York

Bill
Chicago

Mike
Los Angeles

Paul
Dublin

SELECT 4

SELECT DISTINCT ON (name) name, city
FROM nam_cit_sal
ORDER BY name, city DESC;

name
city

Adam
New York

Bill
Yokohama

Mike
Los Angeles

Paul
Paris

SELECT 4

name	salary
Bill	45000
Paul	85000
Bill	50000
Mike	67000
Paul	55000
Bill	35000
Paul	35000
Paul	80000
Adam	75000