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?.
select version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE tab
( version int primary key,
  col_A TEXT,
  col_B TEXT,
  col_C TEXT
);
insert into tab (version, col_A, col_B, col_C)
  select i, 
    case when random() < 0.5 then 'A' ||  floor(random()*10::int) end,
    case when random() < 0.5 then 'B' ||  floor(random()*10::int) end,
    case when random() < 0.5 then 'C' ||  floor(random()*10::int) end
    from generate_series(1, 99999) as g(i) ;
99999 rows affected

create index ix_va on tab (version) WHERE (col_a IS NOT NULL) ;
create index ix_vb on tab (version) WHERE (col_b IS NOT NULL) ;
create index ix_vc on tab (version) WHERE (col_c IS NOT NULL) ;

create index ix_version_include_a on tab (version) INCLUDE (col_a) WHERE (col_a IS NOT NULL) ;
create index ix_version_include_b on tab (version) INCLUDE (col_b) WHERE (col_b IS NOT NULL) ;
create index ix_version_include_c on tab (version) INCLUDE (col_c) WHERE (col_c IS NOT NULL) ;

create index tag_version_desc_x on tab (version desc);
CREATE OR REPLACE FUNCTION test_get_lnn()
 RETURNS TABLE(col_a text, col_b text, col_c text)
 LANGUAGE plpgsql
AS $function$
  declare
    t_a text := null;
    t_b text := null;
    t_c text := null;
    r record;
  begin
    for r in
      select t.col_A, t.col_B, t.col_C from tab t order by version desc
    loop
      if t_a is null then t_a := r.col_A; end if;
      if t_b is null then t_b := r.col_B; end if;
      if t_c is null then t_c := r.col_C; end if;
      exit when t_a is not null and t_b is not null and t_c is not null;
    end loop;
    return query select t_a, t_b, t_c;
  end;
$function$
SELECT  (
        SELECT  col_a
        FROM    tab
        WHERE   col_a IS NOT NULL
        ORDER BY
                version DESC
        LIMIT 1
        ),
        (
        SELECT  col_b
        FROM    tab
        WHERE   col_b IS NOT NULL
        ORDER BY
                version DESC
        LIMIT 1
        ),
        (
        SELECT  col_c
        FROM    tab
        WHERE   col_c IS NOT NULL
        ORDER BY
                version DESC
        LIMIT 1
        ),
        current_time,
        'Quassnoi Option 1' author;


col_a
col_b
col_c
current_time
author


A0
B7
C3
13:57:52.484423+00
Quassnoi Option 1


SELECT  a.col_a, b.col_b, c.col_c, current_time, 'Quassnoi Option 2' author
FROM    (
        SELECT  last_a, last_b, last_c
        FROM    (
                SELECT  *,
                        MAX(version) FILTER (WHERE col_a IS NOT NULL) OVER w AS last_a,
                        MAX(version) FILTER (WHERE col_b IS NOT NULL) OVER w AS last_b,
                        MAX(version) FILTER (WHERE col_c IS NOT NULL) OVER w AS last_c
                FROM    tab
                WINDOW  w AS (ORDER BY version DESC)
                ) q
        WHERE   (last_a, last_b, last_c) IS NOT NULL
        ORDER BY
                version DESC
        LIMIT 1
        ) q
JOIN    tab a
ON      a.version = q.last_a
JOIN    tab b
ON      b.version = q.last_b
JOIN    tab c
ON      c.version = q.last_c ;


col_a
col_b
col_c
current_time
author


A0
B7
C3
13:57:52.486065+00
Quassnoi Option 2


SELECT DISTINCT
    FIRST_VALUE(col_A)
      OVER (ORDER BY CASE WHEN col_A IS NULL THEN 0 ELSE version END DESC)
      AS LastA,
    FIRST_VALUE(col_B)
      OVER (ORDER BY CASE WHEN col_B IS NULL THEN 0 ELSE version END DESC)
      AS LastB,
    FIRST_VALUE(col_C)
      OVER (ORDER BY CASE WHEN col_C IS NULL THEN 0 ELSE version END DESC)
      AS LastC,
    current_time,
    'J.D.' author
    FROM tab ;


lasta
lastb
lastc
current_time
author


A0
B7
C3
13:57:52.48715+00
J.D.


select *, current_time, 'Gerard H. Pille' author from test_get_lnn();


col_a
col_b
col_c
current_time
author


A0
B7
C3
13:57:55.114116+00
Gerard H. Pille