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.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE nam_cit_sal_bis
(
  name   TEXT NOT NULL,
  city   TEXT NOT NULL,
  salary INTEGER NOT NULL,
  CONSTRAINT ncs_pk PRIMARY KEY (name, city)
);
INSERT INTO nam_cit_sal_bis
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);
9 rows affected

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

SELECT 
  DISTINCT name, salary
FROM nam_cit_sal_bis;


name
salary


Bill
45000

Paul
85000

Bill
50000

Mike
67000

Paul
55000

Bill
35000

Paul
35000

Paul
80000

Adam
75000


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


name
salary


Adam
75000

Bill
50000

Mike
67000

Paul
55000


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


name
salary


Paul
85000

Mike
67000

Bill
35000

Adam
75000


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


name
city


Adam
New York

Bill
Chicago

Mike
Los Angeles

Paul
Dublin


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


name
city


Adam
New York

Bill
Yokohama

Mike
Los Angeles

Paul
Paris

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