Postgres 12

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 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 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)
VALUES
(1, 'A1', 'B1', null),
(2, 'A2', 'B2', null),
(3, 'A3', 'B3', null),
(4, 'A5', null, 'C1'),
(5, 'A1', null, null);
5 rows affected

SELECT 
  *
FROM tab 
ORDER BY version;


version
col_a
col_b
col_c


1
A1
B1
null

2
A2
B2
null

3
A3
B3
null

4
A5
null
C1

5
A1
null
null


-- Quassnoi Option 1
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
        ) ;


col_a
col_b
col_c


A1
B3
C1


-- Quassnoi Option 2
SELECT  a.col_a, b.col_b, c.col_c
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


A1
B3
C1


-- J.D.
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
FROM tab ;


lasta
lastb
lastc


A1
B3
C1


create or replace function test_get_lnn ()
  returns table(col_A text, col_B text, col_C text)
language plpgsql
as $$
  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;
$$
;
-- Gerard H. Pille
select * from test_get_lnn() ;


col_a
col_b
col_c


A1
B3
C1


-- Gerard H. Pille
select * from test_get_lnn() ;


col_a
col_b
col_c


A1
B3
C1


-- adding 1k rows with some nulls
insert into tab (version, col_A, col_B, col_C)
select i, 
    case when random() < 0.5 then (random()*100)::int end,
    case when random() < 0.5 then (random()*100)::int end,
    case when random() < 0.5 then (random()*100)::int end
from generate_series(6, 1000) as g(i) ;
995 rows affected

create unique index uq_version on tab (version) ;
select 'Performance Analysis (EXPLAIN (ANALYZE, TIMING OFF) below' ;


?column?


Performance Analysis (EXPLAIN (ANALYZE, TIMING OFF) below


-- Quassnoi Option 1
EXPLAIN (ANALYZE, TIMING OFF)
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
        ) ;


QUERY PLAN


Result  (cost=0.99..1.00 rows=1 width=96) (actual rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Limit  (cost=0.28..0.33 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Scan Backward using uq_version on tab  (cost=0.28..55.27 rows=995 width=36) (actual rows=1 loops=1)

                Filter: (col_a IS NOT NULL)

                Rows Removed by Filter: 2

  InitPlan 2 (returns $1)

    ->  Limit  (cost=0.28..0.33 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Scan Backward using uq_version on tab tab_1  (cost=0.28..55.27 rows=995 width=36) (actual rows=1 loops=1)

                Filter: (col_b IS NOT NULL)

                Rows Removed by Filter: 7

  InitPlan 3 (returns $2)

    ->  Limit  (cost=0.28..0.33 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Scan Backward using uq_version on tab tab_2  (cost=0.28..55.27 rows=995 width=36) (actual rows=1 loops=1)

                Filter: (col_c IS NOT NULL)

Planning Time: 0.323 ms

Execution Time: 0.190 ms


-- Quassnoi Option 2
EXPLAIN (ANALYZE, TIMING OFF)
SELECT  a.col_a, b.col_b, c.col_c
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


QUERY PLAN


Nested Loop  (cost=1.10..25.30 rows=1 width=96) (actual rows=1 loops=1)

  ->  Nested Loop  (cost=0.83..16.99 rows=1 width=68) (actual rows=1 loops=1)

        ->  Nested Loop  (cost=0.55..8.68 rows=1 width=40) (actual rows=1 loops=1)

              ->  Limit  (cost=0.28..0.36 rows=1 width=16) (actual rows=1 loops=1)

                    ->  Subquery Scan on q  (cost=0.28..85.28 rows=985 width=16) (actual rows=1 loops=1)

                          Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL))

                          Rows Removed by Filter: 7

                          ->  WindowAgg  (cost=0.28..75.28 rows=1000 width=112) (actual rows=8 loops=1)

                                ->  Index Scan Backward using uq_version on tab  (cost=0.28..55.27 rows=1000 width=100) (actual rows=9 loops=1)

              ->  Index Scan using uq_version on tab a  (cost=0.28..8.29 rows=1 width=36) (actual rows=1 loops=1)

                    Index Cond: (version = q.last_a)

        ->  Index Scan using uq_version on tab b  (cost=0.28..8.29 rows=1 width=36) (actual rows=1 loops=1)

              Index Cond: (version = q.last_b)

  ->  Index Scan using uq_version on tab c  (cost=0.28..8.29 rows=1 width=36) (actual rows=1 loops=1)

        Index Cond: (version = q.last_c)

Planning Time: 0.376 ms

Execution Time: 0.230 ms


-- J.D.
EXPLAIN (ANALYZE, TIMING OFF)
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
FROM tab ;


QUERY PLAN


HashAggregate  (cost=224.49..226.49 rows=200 width=108) (actual rows=1 loops=1)

  Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?))

  ->  WindowAgg  (cost=199.49..216.99 rows=1000 width=108) (actual rows=1000 loops=1)

        ->  Sort  (cost=199.49..201.99 rows=1000 width=172) (actual rows=1000 loops=1)

              Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC

              Sort Method: quicksort  Memory: 103kB

              ->  WindowAgg  (cost=132.16..149.66 rows=1000 width=172) (actual rows=1000 loops=1)

                    ->  Sort  (cost=132.16..134.66 rows=1000 width=140) (actual rows=1000 loops=1)

                          Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC

                          Sort Method: quicksort  Memory: 99kB

                          ->  WindowAgg  (cost=64.83..82.33 rows=1000 width=140) (actual rows=1000 loops=1)

                                ->  Sort  (cost=64.83..67.33 rows=1000 width=108) (actual rows=1000 loops=1)

                                      Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC

                                      Sort Method: quicksort  Memory: 86kB

                                      ->  Seq Scan on tab  (cost=0.00..15.00 rows=1000 width=108) (actual rows=1000 loops=1)

Planning Time: 0.097 ms

Execution Time: 4.216 ms


-- Gerard H. Pille
EXPLAIN (ANALYZE, TIMING OFF)
select * from test_get_lnn() ;


QUERY PLAN


Function Scan on test_get_lnn  (cost=0.25..10.25 rows=1000 width=96) (actual rows=1 loops=1)

Planning Time: 0.021 ms

Execution Time: 0.233 ms


-- adding 20k rows with nulls
insert into tab (version, col_A, col_B, col_C)
select i, null, null, null
from generate_series(1001, 20000) as g(i) ;
19000 rows affected

-- Quassnoi Option 1
EXPLAIN (ANALYZE, TIMING OFF)
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
        ) ;


QUERY PLAN


Result  (cost=1.01..1.02 rows=1 width=96) (actual rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Limit  (cost=0.29..0.34 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Scan Backward using uq_version on tab  (cost=0.29..851.29 rows=17711 width=36) (actual rows=1 loops=1)

                Filter: (col_a IS NOT NULL)

                Rows Removed by Filter: 19002

  InitPlan 2 (returns $1)

    ->  Limit  (cost=0.29..0.34 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Scan Backward using uq_version on tab tab_1  (cost=0.29..851.29 rows=17711 width=36) (actual rows=1 loops=1)

                Filter: (col_b IS NOT NULL)

                Rows Removed by Filter: 19007

  InitPlan 3 (returns $2)

    ->  Limit  (cost=0.29..0.34 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Scan Backward using uq_version on tab tab_2  (cost=0.29..851.29 rows=17711 width=36) (actual rows=1 loops=1)

                Filter: (col_c IS NOT NULL)

                Rows Removed by Filter: 19000

Planning Time: 0.284 ms

Execution Time: 10.848 ms


-- Quassnoi Option 2
EXPLAIN (ANALYZE, TIMING OFF)
SELECT  a.col_a, b.col_b, c.col_c
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 ;


QUERY PLAN


Nested Loop  (cost=1.15..25.29 rows=1 width=96) (actual rows=1 loops=1)

  ->  Nested Loop  (cost=0.86..16.99 rows=1 width=68) (actual rows=1 loops=1)

        ->  Nested Loop  (cost=0.57..8.68 rows=1 width=40) (actual rows=1 loops=1)

              ->  Limit  (cost=0.29..0.37 rows=1 width=16) (actual rows=1 loops=1)

                    ->  Subquery Scan on q  (cost=0.29..1385.29 rows=17534 width=16) (actual rows=1 loops=1)

                          Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL))

                          Rows Removed by Filter: 19007

                          ->  WindowAgg  (cost=0.29..1207.29 rows=17800 width=112) (actual rows=19008 loops=1)

                                ->  Index Scan Backward using uq_version on tab  (cost=0.29..851.29 rows=17800 width=100) (actual rows=19009 loops=1)

              ->  Index Scan using uq_version on tab a  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

                    Index Cond: (version = q.last_a)

        ->  Index Scan using uq_version on tab b  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

              Index Cond: (version = q.last_b)

  ->  Index Scan using uq_version on tab c  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

        Index Cond: (version = q.last_c)

Planning Time: 0.383 ms

Execution Time: 15.882 ms


-- J.D.
EXPLAIN (ANALYZE, TIMING OFF)
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
FROM tab ;


QUERY PLAN


HashAggregate  (cost=5104.93..5122.73 rows=1780 width=108) (actual rows=1 loops=1)

  Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?))

  ->  WindowAgg  (cost=4659.93..4971.43 rows=17800 width=108) (actual rows=20000 loops=1)

        ->  Sort  (cost=4659.93..4704.43 rows=17800 width=172) (actual rows=20000 loops=1)

              Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC

              Sort Method: quicksort  Memory: 2331kB

              ->  WindowAgg  (cost=3091.79..3403.29 rows=17800 width=172) (actual rows=20000 loops=1)

                    ->  Sort  (cost=3091.79..3136.29 rows=17800 width=140) (actual rows=20000 loops=1)

                          Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC

                          Sort Method: quicksort  Memory: 1733kB

                          ->  WindowAgg  (cost=1523.64..1835.14 rows=17800 width=140) (actual rows=20000 loops=1)

                                ->  Sort  (cost=1523.64..1568.14 rows=17800 width=108) (actual rows=20000 loops=1)

                                      Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC

                                      Sort Method: quicksort  Memory: 1721kB

                                      ->  Seq Scan on tab  (cost=0.00..267.00 rows=17800 width=108) (actual rows=20000 loops=1)

Planning Time: 0.112 ms

Execution Time: 90.156 ms


-- Gerard H. Pille
EXPLAIN (ANALYZE, TIMING OFF)
select * from test_get_lnn() ;


QUERY PLAN


Function Scan on test_get_lnn  (cost=0.25..10.25 rows=1000 width=96) (actual rows=1 loops=1)

Planning Time: 0.064 ms

Execution Time: 55.720 ms


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) ;
select 'Adding partial indexes:  (version) WHERE (column IS NOT NULL)'


?column?


Adding partial indexes:  (version) WHERE (column IS NOT NULL)


-- Quassnoi Option 1
EXPLAIN (ANALYZE, TIMING OFF)
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
        ) ;


QUERY PLAN


Result  (cost=0.90..0.91 rows=1 width=96) (actual rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Limit  (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Scan Backward using ix_va on tab  (cost=0.27..573.68 rows=19900 width=36) (actual rows=1 loops=1)

  InitPlan 2 (returns $1)

    ->  Limit  (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Scan Backward using ix_vb on tab tab_1  (cost=0.27..573.63 rows=19900 width=36) (actual rows=1 loops=1)

  InitPlan 3 (returns $2)

    ->  Limit  (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Scan Backward using ix_vc on tab tab_2  (cost=0.27..573.76 rows=19900 width=36) (actual rows=1 loops=1)

Planning Time: 0.688 ms

Execution Time: 0.162 ms


-- Quassnoi Option 2
EXPLAIN (ANALYZE, TIMING OFF)
SELECT  a.col_a, b.col_b, c.col_c
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 ;


QUERY PLAN


Nested Loop  (cost=1.15..25.29 rows=1 width=96) (actual rows=1 loops=1)

  ->  Nested Loop  (cost=0.86..16.98 rows=1 width=68) (actual rows=1 loops=1)

        ->  Nested Loop  (cost=0.57..8.68 rows=1 width=40) (actual rows=1 loops=1)

              ->  Limit  (cost=0.29..0.36 rows=1 width=16) (actual rows=1 loops=1)

                    ->  Subquery Scan on q  (cost=0.29..1484.29 rows=19701 width=16) (actual rows=1 loops=1)

                          Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL))

                          Rows Removed by Filter: 19007

                          ->  WindowAgg  (cost=0.29..1284.29 rows=20000 width=112) (actual rows=19008 loops=1)

                                ->  Index Scan Backward using uq_version on tab  (cost=0.29..884.29 rows=20000 width=100) (actual rows=19009 loops=1)

              ->  Index Scan using uq_version on tab a  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

                    Index Cond: (version = q.last_a)

        ->  Index Scan using uq_version on tab b  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

              Index Cond: (version = q.last_b)

  ->  Index Scan using uq_version on tab c  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

        Index Cond: (version = q.last_c)

Planning Time: 0.502 ms

Execution Time: 16.198 ms


-- J.D.
EXPLAIN (ANALYZE, TIMING OFF)
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
FROM tab ;


QUERY PLAN


HashAggregate  (cost=5775.31..5795.31 rows=2000 width=108) (actual rows=1 loops=1)

  Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?))

  ->  WindowAgg  (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=20000 loops=1)

        ->  Sort  (cost=5275.31..5325.31 rows=20000 width=172) (actual rows=20000 loops=1)

              Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC

              Sort Method: quicksort  Memory: 2331kB

              ->  WindowAgg  (cost=3496.54..3846.54 rows=20000 width=172) (actual rows=20000 loops=1)

                    ->  Sort  (cost=3496.54..3546.54 rows=20000 width=140) (actual rows=20000 loops=1)

                          Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC

                          Sort Method: quicksort  Memory: 1733kB

                          ->  WindowAgg  (cost=1717.77..2067.77 rows=20000 width=140) (actual rows=20000 loops=1)

                                ->  Sort  (cost=1717.77..1767.77 rows=20000 width=108) (actual rows=20000 loops=1)

                                      Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC

                                      Sort Method: quicksort  Memory: 1721kB

                                      ->  Seq Scan on tab  (cost=0.00..289.00 rows=20000 width=108) (actual rows=20000 loops=1)

Planning Time: 0.110 ms

Execution Time: 66.894 ms


-- Gerard H. Pille
EXPLAIN (ANALYZE, TIMING OFF)
select * from test_get_lnn() ;


QUERY PLAN


Function Scan on test_get_lnn  (cost=0.25..10.25 rows=1000 width=96) (actual rows=1 loops=1)

Planning Time: 0.044 ms

Execution Time: 78.725 ms


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) ;
select 'Adding partial indexex:  (version) INCLUDE (column) WHERE (column IS NOT NULL)'


?column?


Adding partial indexex:  (version) INCLUDE (column) WHERE (column IS NOT NULL)


-- Quassnoi Option 1
EXPLAIN (ANALYZE, TIMING OFF)
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
        ) ;


QUERY PLAN


Result  (cost=0.90..0.91 rows=1 width=96) (actual rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Limit  (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Only Scan Backward using ix_version_include_a on tab  (cost=0.27..573.68 rows=19900 width=36) (actual rows=1 loops=1)

                Heap Fetches: 1

  InitPlan 2 (returns $1)

    ->  Limit  (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Only Scan Backward using ix_version_include_b on tab tab_1  (cost=0.27..573.63 rows=19900 width=36) (actual rows=1 loops=1)

                Heap Fetches: 1

  InitPlan 3 (returns $2)

    ->  Limit  (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Only Scan Backward using ix_version_include_c on tab tab_2  (cost=0.27..573.76 rows=19900 width=36) (actual rows=1 loops=1)

                Heap Fetches: 1

Planning Time: 0.642 ms

Execution Time: 0.221 ms


-- Quassnoi Option 2
EXPLAIN (ANALYZE, TIMING OFF)
SELECT  a.col_a, b.col_b, c.col_c
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 ;


QUERY PLAN


Nested Loop  (cost=1.15..25.29 rows=1 width=96) (actual rows=1 loops=1)

  ->  Nested Loop  (cost=0.86..16.98 rows=1 width=68) (actual rows=1 loops=1)

        ->  Nested Loop  (cost=0.57..8.68 rows=1 width=40) (actual rows=1 loops=1)

              ->  Limit  (cost=0.29..0.36 rows=1 width=16) (actual rows=1 loops=1)

                    ->  Subquery Scan on q  (cost=0.29..1484.29 rows=19701 width=16) (actual rows=1 loops=1)

                          Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL))

                          Rows Removed by Filter: 19007

                          ->  WindowAgg  (cost=0.29..1284.29 rows=20000 width=112) (actual rows=19008 loops=1)

                                ->  Index Scan Backward using uq_version on tab  (cost=0.29..884.29 rows=20000 width=100) (actual rows=19009 loops=1)

              ->  Index Scan using uq_version on tab a  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

                    Index Cond: (version = q.last_a)

        ->  Index Scan using uq_version on tab b  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

              Index Cond: (version = q.last_b)

  ->  Index Scan using uq_version on tab c  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

        Index Cond: (version = q.last_c)

Planning Time: 0.505 ms

Execution Time: 19.423 ms


-- J.D.
EXPLAIN (ANALYZE, TIMING OFF)
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
FROM tab ;


QUERY PLAN


HashAggregate  (cost=5775.31..5795.31 rows=2000 width=108) (actual rows=1 loops=1)

  Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?))

  ->  WindowAgg  (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=20000 loops=1)

        ->  Sort  (cost=5275.31..5325.31 rows=20000 width=172) (actual rows=20000 loops=1)

              Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC

              Sort Method: quicksort  Memory: 2331kB

              ->  WindowAgg  (cost=3496.54..3846.54 rows=20000 width=172) (actual rows=20000 loops=1)

                    ->  Sort  (cost=3496.54..3546.54 rows=20000 width=140) (actual rows=20000 loops=1)

                          Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC

                          Sort Method: quicksort  Memory: 1733kB

                          ->  WindowAgg  (cost=1717.77..2067.77 rows=20000 width=140) (actual rows=20000 loops=1)

                                ->  Sort  (cost=1717.77..1767.77 rows=20000 width=108) (actual rows=20000 loops=1)

                                      Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC

                                      Sort Method: quicksort  Memory: 1721kB

                                      ->  Seq Scan on tab  (cost=0.00..289.00 rows=20000 width=108) (actual rows=20000 loops=1)

Planning Time: 0.111 ms

Execution Time: 60.521 ms


-- Gerard H. Pille
EXPLAIN (ANALYZE, TIMING OFF)
select * from test_get_lnn() ;


QUERY PLAN


Function Scan on test_get_lnn  (cost=0.25..10.25 rows=1000 width=96) (actual rows=1 loops=1)

Planning Time: 0.030 ms

Execution Time: 53.201 ms


-- update with some non-null in the last 100 rows
update tab
set 
    col_a = case when random() < 0.1 then (random()*100)::int end,
    col_b = case when random() < 0.1 then (random()*100)::int end,
    col_c = case when random() < 0.1 then (random()*100)::int end
from generate_series(19900, 20000) as g(i)
where tab.version = g.i ;
101 rows affected

-- Quassnoi Option 1
EXPLAIN (ANALYZE, TIMING OFF)
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
        ) ;


QUERY PLAN


Result  (cost=0.90..0.91 rows=1 width=96) (actual rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Limit  (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Only Scan Backward using ix_version_include_a on tab  (cost=0.27..579.92 rows=20124 width=36) (actual rows=1 loops=1)

                Heap Fetches: 1

  InitPlan 2 (returns $1)

    ->  Limit  (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Only Scan Backward using ix_version_include_b on tab tab_1  (cost=0.27..579.87 rows=20124 width=36) (actual rows=1 loops=1)

                Heap Fetches: 1

  InitPlan 3 (returns $2)

    ->  Limit  (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1)

          ->  Index Only Scan Backward using ix_version_include_c on tab tab_2  (cost=0.27..580.00 rows=20124 width=36) (actual rows=1 loops=1)

                Heap Fetches: 1

Planning Time: 0.341 ms

Execution Time: 0.071 ms


-- Quassnoi Option 2
EXPLAIN (ANALYZE, TIMING OFF)
SELECT  a.col_a, b.col_b, c.col_c
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 ;


QUERY PLAN


Nested Loop  (cost=1.15..25.29 rows=1 width=96) (actual rows=1 loops=1)

  ->  Nested Loop  (cost=0.86..16.98 rows=1 width=68) (actual rows=1 loops=1)

        ->  Nested Loop  (cost=0.57..8.68 rows=1 width=40) (actual rows=1 loops=1)

              ->  Limit  (cost=0.29..0.36 rows=1 width=16) (actual rows=1 loops=1)

                    ->  Subquery Scan on q  (cost=0.29..1498.41 rows=19923 width=16) (actual rows=1 loops=1)

                          Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL))

                          Rows Removed by Filter: 7

                          ->  WindowAgg  (cost=0.29..1296.16 rows=20225 width=112) (actual rows=8 loops=1)

                                ->  Index Scan Backward using uq_version on tab  (cost=0.29..891.66 rows=20225 width=100) (actual rows=9 loops=1)

              ->  Index Scan using uq_version on tab a  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

                    Index Cond: (version = q.last_a)

        ->  Index Scan using uq_version on tab b  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

              Index Cond: (version = q.last_b)

  ->  Index Scan using uq_version on tab c  (cost=0.29..8.30 rows=1 width=36) (actual rows=1 loops=1)

        Index Cond: (version = q.last_c)

Planning Time: 0.487 ms

Execution Time: 0.148 ms


-- J.D.
EXPLAIN (ANALYZE, TIMING OFF)
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
FROM tab ;


QUERY PLAN


HashAggregate  (cost=5845.18..5865.40 rows=2022 width=108) (actual rows=1 loops=1)

  Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?))

  ->  WindowAgg  (cost=5339.56..5693.49 rows=20225 width=108) (actual rows=20000 loops=1)

        ->  Sort  (cost=5339.56..5390.12 rows=20225 width=172) (actual rows=20000 loops=1)

              Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC

              Sort Method: quicksort  Memory: 2331kB

              ->  WindowAgg  (cost=3539.14..3893.08 rows=20225 width=172) (actual rows=20000 loops=1)

                    ->  Sort  (cost=3539.14..3589.70 rows=20225 width=140) (actual rows=20000 loops=1)

                          Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC

                          Sort Method: quicksort  Memory: 1734kB

                          ->  WindowAgg  (cost=1738.73..2092.66 rows=20225 width=140) (actual rows=20000 loops=1)

                                ->  Sort  (cost=1738.73..1789.29 rows=20225 width=108) (actual rows=20000 loops=1)

                                      Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC

                                      Sort Method: quicksort  Memory: 1721kB

                                      ->  Seq Scan on tab  (cost=0.00..292.25 rows=20225 width=108) (actual rows=20000 loops=1)

Planning Time: 0.142 ms

Execution Time: 62.062 ms


-- Gerard H. Pille
EXPLAIN (ANALYZE, TIMING OFF)
select * from test_get_lnn() ;


QUERY PLAN


Function Scan on test_get_lnn  (cost=0.25..10.25 rows=1000 width=96) (actual rows=1 loops=1)

Planning Time: 0.065 ms

Execution Time: 0.249 ms
version	col_a	col_b	col_c
1	A1	B1	null
2	A2	B2	null
3	A3	B3	null
4	A5	null	C1
5	A1	null	null