Postgres 13

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 (
  t_id serial PRIMARY KEY
, ts timestamp
, a text
, b text
, c text
);

INSERT INTO t(ts,a,b,c)  VALUES
  ('2013-10-15 11:45:00', NULL,  'timtim', 'fred')
, ('2013-10-15 13:00:00', 'tune', 'reco',  NULL  )
, ('2013-10-16 12:00:00', 'abc',  NULL,    NULL  )
, ('2013-10-16 13:00:00', NULL,  'died',   NULL  );

CREATE TABLE



INSERT 0 4


WITH cte AS (
   SELECT *
        , count(a) OVER w AS grp_a
        , count(b) OVER w AS grp_b
        , count(c) OVER w AS grp_c
   FROM   t
   WINDOW w AS (ORDER BY ts)
   ) 
SELECT ts
     , max(a) OVER (PARTITION BY grp_a) AS a
     , max(b) OVER (PARTITION BY grp_b) AS b
     , max(c) OVER (PARTITION BY grp_c) AS c
FROM   cte
ORDER  BY ts DESC
LIMIT  1;


ts
a
b
c


2013-10-16 13:00:00
abc
died
fred



SELECT 1


SELECT ts
      , COALESCE(a, (SELECT a FROM t WHERE a IS NOT NULL ORDER BY ts DESC LIMIT 1)) AS a
      , COALESCE(b, (SELECT b FROM t WHERE b IS NOT NULL ORDER BY ts DESC LIMIT 1)) AS b
      , COALESCE(c, (SELECT c FROM t WHERE c IS NOT NULL ORDER BY ts DESC LIMIT 1)) AS c
FROM   t
ORDER  BY ts DESC
LIMIT  1;


ts
a
b
c


2013-10-16 13:00:00
abc
died
fred



SELECT 1


SELECT (SELECT ts FROM t                     ORDER BY ts DESC LIMIT 1) AS ts
     , (SELECT a  FROM t WHERE a IS NOT NULL ORDER BY ts DESC LIMIT 1) AS a
     , (SELECT b  FROM t WHERE b IS NOT NULL ORDER BY ts DESC LIMIT 1) AS b
     , (SELECT c  FROM t WHERE c IS NOT NULL ORDER BY ts DESC LIMIT 1) AS c;


ts
a
b
c


2013-10-16 13:00:00
abc
died
fred



SELECT 1