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 text, col3 date);
INSERT INTO test VALUES
  (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');

CREATE TABLE



INSERT 0 6


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


rn
col1
col2
col3


1
2
xyz
2015-09-13

1
1
abc
2015-09-11

1
3
tcs
2015-01-18



SELECT 3


SELECT *
FROM  (
   SELECT DISTINCT ON (col1)
          col1, col2, col3
   FROM   test
   ORDER  BY col1, col3 DESC
   ) sub
ORDER  BY col3 DESC, col1;


col1
col2
col3


2
xyz
2015-09-13

1
abc
2015-09-11

3
tcs
2015-01-18



SELECT 3