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

CREATE TABLE t(
   city1 VARCHAR(32) NOT NULL
  ,city2 VARCHAR(11)
  ,dist  INTEGER 
);

CREATE TABLE

INSERT INTO t(city1,city2,dist) VALUES ('New York','Berlin',7900);

INSERT 0 1

INSERT INTO t(city1,city2,dist) VALUES ('Berlin','New York',7900);

INSERT 0 1

INSERT INTO t(city1,city2,dist) VALUES ('Oregon','Ohio',5700);

INSERT 0 1

INSERT INTO t(city1,city2,dist) VALUES ('Montreal','Rio',5700);

INSERT 0 1

INSERT INTO t(city1,city2,dist) VALUES ('Ohio','Oregon',5700);

INSERT 0 1

INSERT INTO t(city1,city2,dist) VALUES ('Rio','Montreal',5700);

INSERT 0 1

INSERT INTO t(city1,city2,dist) VALUES ('Moscow','Tokyo',4200);

INSERT 0 1

INSERT INTO t(city1,city2,dist) VALUES ('Tokyo','Moscow',4200);

INSERT 0 1

INSERT INTO t(city1,city2,dist) VALUES ('Conce','Santiago',500);

INSERT 0 1

INSERT INTO t(city1,city2,dist) VALUES ('Talca','Linares',80);

INSERT 0 1

SELECT t1.*
FROM t t1
LEFT JOIN t t2 ON t1.city1 = t2.city2 and t2.city2 = t1.city1 AND t1.dist = t2.dist
WHERE t2.city1 IS NULL 
  OR t1.city1 > t1.city2

city1
city2
dist

Conce
Santiago
500

New York
Berlin
7900

Oregon
Ohio
5700

Rio
Montreal
5700

Talca
Linares
80

Tokyo
Moscow
4200

SELECT 6

SELECT t1.*
FROM t t1
LEFT JOIN t t2 ON t1.city1 = t2.city2 and t2.city2 = t1.city1 AND t1.dist = t2.dist
WHERE t2.city1 IS NULL 
  OR t2.city1 > t2.city2

city1
city2
dist

Berlin
New York
7900

Conce
Santiago
500

Montreal
Rio
5700

Moscow
Tokyo
4200

Ohio
Oregon
5700

Talca
Linares
80

SELECT 6

city1	city2	dist
Conce	Santiago	500
New York	Berlin	7900
Oregon	Ohio	5700
Rio	Montreal	5700
Talca	Linares	80
Tokyo	Moscow	4200