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

create table test(col1 int, col2 varchar,col3 date);
insert into test values (1,'abc','2015-09-10');
insert into test values (1,'abc','2015-09-11');
insert into test values (2,'xyz','2015-09-12');
insert into test values (2,'xyz','2015-09-13');
insert into test values (3,'tcs','2015-01-15');
insert into test values (3,'tcs','2015-01-18');

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT * FROM test


col1
col2
col3


1
abc
2015-09-10

1
abc
2015-09-11

2
xyz
2015-09-12

2
xyz
2015-09-13

3
tcs
2015-01-15

3
tcs
2015-01-18



SELECT 6


WITH cte AS
(
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS rn,
    col1, col2, col3 
  FROM test
  ORDER BY col3 DESC
)
SELECT * FROM cte 
WHERE rn = 1
ORDER BY col3


rn
col1
col2
col3


1
3
tcs
2015-01-18

1
1
abc
2015-09-11

1
2
xyz
2015-09-13



SELECT 3


SELECT x, y, mc FROM
(
  SELECT col1 AS x, col2 AS y, MAX(col3) AS mc
  FROM test
  GROUP BY col1, col2
) AS tab
ORDER BY mc


x
y
mc


3
tcs
2015-01-18

1
abc
2015-09-11

2
xyz
2015-09-13



SELECT 3

col1	col2	col3
1	abc	2015-09-10
1	abc	2015-09-11
2	xyz	2015-09-12
2	xyz	2015-09-13
3	tcs	2015-01-15
3	tcs	2015-01-18