Postgres 10

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 TestTable (ID INT,   code CHAR(1), first_origin_id INT,   delivery_id INT,
source INT);

CREATE TABLE


INSERT INTO TestTable (ID, code, first_origin_id, delivery_id, source)
SELECT 1, 'A', 10, 20, 0 UNION
SELECT 2, 'B', 10, 20, 1 UNION
SELECT 3, 'C', 11, 21, 1 UNION
SELECT 4, 'D', 12, 22, 1 UNION
SELECT 5, 'E', 12, 22, 0

INSERT 0 5


SELECT ID, code, first_origin_id, delivery_id, source FROM (
  SELECT ID, code, first_origin_id, delivery_id, source, 
  ROW_NUMBER() OVER (PARTITION BY first_origin_id, delivery_id ORDER BY Source, ID) AS RowNumber
  FROM TestTable 
) Q
WHERE Q.RowNumber = 1


id
code
first_origin_id
delivery_id
source


1
A
10
20
0

3
C
11
21
1

5
E
12
22
0



SELECT 3