Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > select version(); > > <pre> > | 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 | > </pre> <!-- --> > CREATE TABLE tab > ( > version INT PRIMARY KEY, > col_A TEXT, > col_B TEXT, > col_C TEXT > ); > > <pre> > ✓ > </pre> <!-- --> > 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); > > <pre> 5 rows affected > </pre> <!-- --> > SELECT > * > FROM tab > ORDER BY version; > > <pre> > version | col_a | col_b | col_c > ------: | :---- | :---- | :---- > 1 | A1 | B1 | <em>null</em> > 2 | A2 | B2 | <em>null</em> > 3 | A3 | B3 | <em>null</em> > 4 | A5 | <em>null</em> | C1 > 5 | A1 | <em>null</em> | <em>null</em> > </pre> <!-- --> > -- 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 > ) ; > > <pre> > col_a | col_b | col_c > :---- | :---- | :---- > A1 | B3 | C1 > </pre> <!-- --> > -- 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 ; > > <pre> > col_a | col_b | col_c > :---- | :---- | :---- > A1 | B3 | C1 > </pre> <!-- --> > -- 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 ; > > <pre> > lasta | lastb | lastc > :---- | :---- | :---- > A1 | B3 | C1 > </pre> <!-- --> > 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() ; > > <pre> > ✓ > > col_a | col_b | col_c > :---- | :---- | :---- > A1 | B3 | C1 > </pre> <!-- --> > -- Gerard H. Pille > select * from test_get_lnn() ; > > <pre> > col_a | col_b | col_c > :---- | :---- | :---- > A1 | B3 | C1 > </pre> <!-- --> > -- 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) ; > > <pre> 995 rows affected > </pre> <!-- --> > create unique index uq_version on tab (version) ; > > <pre> > ✓ > </pre> <!-- --> > select 'Performance Analysis (EXPLAIN (ANALYZE, TIMING OFF) below' ; > > <pre> > | ?column? | > | :-------------------------------------------------------- | > | Performance Analysis (EXPLAIN (ANALYZE, TIMING OFF) below | > </pre> <!-- --> > -- 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 > ) ; > > <pre> > | QUERY PLAN | > | :---------------------------------------------------------------------------------------------------------------------------- | > | Result (cost=0.99..1.00 rows=1 width=96) (actual rows=1 loops=1) | > | InitPlan 1 (returns $0) | > | -&gt; Limit (cost=0.28..0.33 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; 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) | > | InitPlan 2 (returns $1) | > | -&gt; Limit (cost=0.28..0.33 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; 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: 2 | > | InitPlan 3 (returns $2) | > | -&gt; Limit (cost=0.28..0.33 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; 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) | > | Rows Removed by Filter: 1 | > | Planning Time: 0.373 ms | > | Execution Time: 0.090 ms | > </pre> <!-- --> > -- 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 > > <pre> > | QUERY PLAN | > | :---------------------------------------------------------------------------------------------------------------------------------------------- | > | Nested Loop (cost=1.10..25.30 rows=1 width=96) (actual rows=1 loops=1) | > | -&gt; Nested Loop (cost=0.83..16.99 rows=1 width=68) (actual rows=1 loops=1) | > | -&gt; Nested Loop (cost=0.55..8.68 rows=1 width=40) (actual rows=1 loops=1) | > | -&gt; Limit (cost=0.28..0.36 rows=1 width=16) (actual rows=1 loops=1) | > | -&gt; 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: 2 | > | -&gt; WindowAgg (cost=0.28..75.28 rows=1000 width=112) (actual rows=3 loops=1) | > | -&gt; Index Scan Backward using uq_version on tab (cost=0.28..55.27 rows=1000 width=100) (actual rows=4 loops=1) | > | -&gt; 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) | > | -&gt; 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) | > | -&gt; 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.406 ms | > | Execution Time: 0.231 ms | > </pre> <!-- --> > -- 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 ; > > <pre> > | 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 (?)) | > | -&gt; WindowAgg (cost=199.49..216.99 rows=1000 width=108) (actual rows=1000 loops=1) | > | -&gt; 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 | > | -&gt; WindowAgg (cost=132.16..149.66 rows=1000 width=172) (actual rows=1000 loops=1) | > | -&gt; 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 | > | -&gt; WindowAgg (cost=64.83..82.33 rows=1000 width=140) (actual rows=1000 loops=1) | > | -&gt; 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: 88kB | > | -&gt; Seq Scan on tab (cost=0.00..15.00 rows=1000 width=108) (actual rows=1000 loops=1) | > | Planning Time: 0.064 ms | > | Execution Time: 5.091 ms | > </pre> <!-- --> > -- Gerard H. Pille > EXPLAIN (ANALYZE, TIMING OFF) > select * from test_get_lnn() ; > > <pre> > | 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.014 ms | > | Execution Time: 0.174 ms | > </pre> <!-- --> > -- 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) ; > > <pre> 19000 rows affected > </pre> <!-- --> > -- 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 > ) ; > > <pre> > | QUERY PLAN | > | :------------------------------------------------------------------------------------------------------------------------------- | > | Result (cost=1.01..1.02 rows=1 width=96) (actual rows=1 loops=1) | > | InitPlan 1 (returns $0) | > | -&gt; Limit (cost=0.29..0.34 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Scan Backward using uq_version on tab (cost=0.29..858.29 rows=17910 width=36) (actual rows=1 loops=1) | > | Filter: (col_a IS NOT NULL) | > | Rows Removed by Filter: 19000 | > | InitPlan 2 (returns $1) | > | -&gt; Limit (cost=0.29..0.34 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Scan Backward using uq_version on tab tab_1 (cost=0.29..858.29 rows=17910 width=36) (actual rows=1 loops=1) | > | Filter: (col_b IS NOT NULL) | > | Rows Removed by Filter: 19002 | > | InitPlan 3 (returns $2) | > | -&gt; Limit (cost=0.29..0.34 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Scan Backward using uq_version on tab tab_2 (cost=0.29..858.29 rows=17910 width=36) (actual rows=1 loops=1) | > | Filter: (col_c IS NOT NULL) | > | Rows Removed by Filter: 19001 | > | Planning Time: 0.186 ms | > | Execution Time: 8.887 ms | > </pre> <!-- --> > -- 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 ; > > <pre> > | QUERY PLAN | > | :---------------------------------------------------------------------------------------------------------------------------------------------------- | > | Nested Loop (cost=1.15..25.29 rows=1 width=96) (actual rows=1 loops=1) | > | -&gt; Nested Loop (cost=0.86..16.99 rows=1 width=68) (actual rows=1 loops=1) | > | -&gt; Nested Loop (cost=0.57..8.68 rows=1 width=40) (actual rows=1 loops=1) | > | -&gt; Limit (cost=0.29..0.37 rows=1 width=16) (actual rows=1 loops=1) | > | -&gt; Subquery Scan on q (cost=0.29..1398.29 rows=17731 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: 19002 | > | -&gt; WindowAgg (cost=0.29..1218.29 rows=18000 width=112) (actual rows=19003 loops=1) | > | -&gt; Index Scan Backward using uq_version on tab (cost=0.29..858.29 rows=18000 width=100) (actual rows=19004 loops=1) | > | -&gt; 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) | > | -&gt; 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) | > | -&gt; 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.327 ms | > | Execution Time: 13.012 ms | > </pre> <!-- --> > -- 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 ; > > <pre> > | QUERY PLAN | > | :------------------------------------------------------------------------------------------------------------------------------ | > | HashAggregate (cost=5166.64..5184.64 rows=1800 width=108) (actual rows=1 loops=1) | > | Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?)) | > | -&gt; WindowAgg (cost=4716.64..5031.64 rows=18000 width=108) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=4716.64..4761.64 rows=18000 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 | > | -&gt; WindowAgg (cost=3129.43..3444.43 rows=18000 width=172) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=3129.43..3174.43 rows=18000 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 | > | -&gt; WindowAgg (cost=1542.21..1857.21 rows=18000 width=140) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=1542.21..1587.21 rows=18000 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: 1722kB | > | -&gt; Seq Scan on tab (cost=0.00..270.00 rows=18000 width=108) (actual rows=20000 loops=1) | > | Planning Time: 0.069 ms | > | Execution Time: 50.184 ms | > </pre> <!-- --> > -- Gerard H. Pille > EXPLAIN (ANALYZE, TIMING OFF) > select * from test_get_lnn() ; > > <pre> > | 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.037 ms | > | Execution Time: 37.447 ms | > </pre> <!-- --> > 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)' > > <pre> > ✓ > > ✓ > > ✓ > > | ?column? | > | :------------------------------------------------------------ | > | Adding partial indexes: (version) WHERE (column IS NOT NULL) | > </pre> <!-- --> > -- 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 > ) ; > > <pre> > | QUERY PLAN | > | :-------------------------------------------------------------------------------------------------------------------------- | > | Result (cost=0.91..0.92 rows=1 width=96) (actual rows=1 loops=1) | > | InitPlan 1 (returns $0) | > | -&gt; Limit (cost=0.28..0.30 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Scan Backward using ix_va on tab (cost=0.28..577.84 rows=19900 width=36) (actual rows=1 loops=1) | > | InitPlan 2 (returns $1) | > | -&gt; Limit (cost=0.28..0.30 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Scan Backward using ix_vb on tab tab_1 (cost=0.28..577.84 rows=19900 width=36) (actual rows=1 loops=1) | > | InitPlan 3 (returns $2) | > | -&gt; Limit (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Scan Backward using ix_vc on tab tab_2 (cost=0.27..577.78 rows=19900 width=36) (actual rows=1 loops=1) | > | Planning Time: 0.645 ms | > | Execution Time: 0.153 ms | > </pre> <!-- --> > -- 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 ; > > <pre> > | QUERY PLAN | > | :---------------------------------------------------------------------------------------------------------------------------------------------------- | > | Nested Loop (cost=1.15..25.29 rows=1 width=96) (actual rows=1 loops=1) | > | -&gt; Nested Loop (cost=0.86..16.98 rows=1 width=68) (actual rows=1 loops=1) | > | -&gt; Nested Loop (cost=0.57..8.68 rows=1 width=40) (actual rows=1 loops=1) | > | -&gt; Limit (cost=0.29..0.36 rows=1 width=16) (actual rows=1 loops=1) | > | -&gt; Subquery Scan on q (cost=0.29..1488.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: 19002 | > | -&gt; WindowAgg (cost=0.29..1288.29 rows=20000 width=112) (actual rows=19003 loops=1) | > | -&gt; Index Scan Backward using uq_version on tab (cost=0.29..888.29 rows=20000 width=100) (actual rows=19004 loops=1) | > | -&gt; 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) | > | -&gt; 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) | > | -&gt; 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.529 ms | > | Execution Time: 16.311 ms | > </pre> <!-- --> > -- 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 ; > > <pre> > | QUERY PLAN | > | :------------------------------------------------------------------------------------------------------------------------------ | > | HashAggregate (cost=5776.31..5796.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 (?)) | > | -&gt; WindowAgg (cost=5276.31..5626.31 rows=20000 width=108) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=5276.31..5326.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 | > | -&gt; WindowAgg (cost=3497.54..3847.54 rows=20000 width=172) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=3497.54..3547.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: 1734kB | > | -&gt; WindowAgg (cost=1718.77..2068.77 rows=20000 width=140) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=1718.77..1768.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: 1722kB | > | -&gt; Seq Scan on tab (cost=0.00..290.00 rows=20000 width=108) (actual rows=20000 loops=1) | > | Planning Time: 0.080 ms | > | Execution Time: 48.109 ms | > </pre> <!-- --> > -- Gerard H. Pille > EXPLAIN (ANALYZE, TIMING OFF) > select * from test_get_lnn() ; > > <pre> > | 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.036 ms | > | Execution Time: 36.740 ms | > </pre> <!-- --> > 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)' > > <pre> > ✓ > > ✓ > > ✓ > > | ?column? | > | :----------------------------------------------------------------------------- | > | Adding partial indexex: (version) INCLUDE (column) WHERE (column IS NOT NULL) | > </pre> <!-- --> > -- 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 > ) ; > > <pre> > | QUERY PLAN | > | :---------------------------------------------------------------------------------------------------------------------------------------------- | > | Result (cost=0.91..0.92 rows=1 width=96) (actual rows=1 loops=1) | > | InitPlan 1 (returns $0) | > | -&gt; Limit (cost=0.28..0.30 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Only Scan Backward using ix_version_include_a on tab (cost=0.28..577.84 rows=19900 width=36) (actual rows=1 loops=1) | > | Heap Fetches: 1 | > | InitPlan 2 (returns $1) | > | -&gt; Limit (cost=0.28..0.30 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Only Scan Backward using ix_version_include_b on tab tab_1 (cost=0.28..577.84 rows=19900 width=36) (actual rows=1 loops=1) | > | Heap Fetches: 1 | > | InitPlan 3 (returns $2) | > | -&gt; Limit (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Only Scan Backward using ix_version_include_c on tab tab_2 (cost=0.27..577.78 rows=19900 width=36) (actual rows=1 loops=1) | > | Heap Fetches: 1 | > | Planning Time: 0.455 ms | > | Execution Time: 0.124 ms | > </pre> <!-- --> > -- 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 ; > > <pre> > | QUERY PLAN | > | :---------------------------------------------------------------------------------------------------------------------------------------------------- | > | Nested Loop (cost=1.15..25.29 rows=1 width=96) (actual rows=1 loops=1) | > | -&gt; Nested Loop (cost=0.86..16.98 rows=1 width=68) (actual rows=1 loops=1) | > | -&gt; Nested Loop (cost=0.57..8.68 rows=1 width=40) (actual rows=1 loops=1) | > | -&gt; Limit (cost=0.29..0.36 rows=1 width=16) (actual rows=1 loops=1) | > | -&gt; Subquery Scan on q (cost=0.29..1488.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: 19002 | > | -&gt; WindowAgg (cost=0.29..1288.29 rows=20000 width=112) (actual rows=19003 loops=1) | > | -&gt; Index Scan Backward using uq_version on tab (cost=0.29..888.29 rows=20000 width=100) (actual rows=19004 loops=1) | > | -&gt; 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) | > | -&gt; 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) | > | -&gt; 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.334 ms | > | Execution Time: 12.051 ms | > </pre> <!-- --> > -- 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 ; > > <pre> > | QUERY PLAN | > | :------------------------------------------------------------------------------------------------------------------------------ | > | HashAggregate (cost=5776.31..5796.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 (?)) | > | -&gt; WindowAgg (cost=5276.31..5626.31 rows=20000 width=108) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=5276.31..5326.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 | > | -&gt; WindowAgg (cost=3497.54..3847.54 rows=20000 width=172) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=3497.54..3547.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: 1734kB | > | -&gt; WindowAgg (cost=1718.77..2068.77 rows=20000 width=140) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=1718.77..1768.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: 1722kB | > | -&gt; Seq Scan on tab (cost=0.00..290.00 rows=20000 width=108) (actual rows=20000 loops=1) | > | Planning Time: 0.108 ms | > | Execution Time: 48.914 ms | > </pre> <!-- --> > -- Gerard H. Pille > EXPLAIN (ANALYZE, TIMING OFF) > select * from test_get_lnn() ; > > <pre> > | 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.036 ms | > | Execution Time: 37.143 ms | > </pre> <!-- --> > -- 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 ; > > <pre> 101 rows affected > </pre> <!-- --> > -- 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 > ) ; > > <pre> > | QUERY PLAN | > | :---------------------------------------------------------------------------------------------------------------------------------------------- | > | Result (cost=0.91..0.92 rows=1 width=96) (actual rows=1 loops=1) | > | InitPlan 1 (returns $0) | > | -&gt; Limit (cost=0.28..0.30 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Only Scan Backward using ix_version_include_a on tab (cost=0.28..577.84 rows=19900 width=36) (actual rows=1 loops=1) | > | Heap Fetches: 1 | > | InitPlan 2 (returns $1) | > | -&gt; Limit (cost=0.28..0.30 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Only Scan Backward using ix_version_include_b on tab tab_1 (cost=0.28..577.84 rows=19900 width=36) (actual rows=1 loops=1) | > | Heap Fetches: 1 | > | InitPlan 3 (returns $2) | > | -&gt; Limit (cost=0.27..0.30 rows=1 width=36) (actual rows=1 loops=1) | > | -&gt; Index Only Scan Backward using ix_version_include_c on tab tab_2 (cost=0.27..577.78 rows=19900 width=36) (actual rows=1 loops=1) | > | Heap Fetches: 1 | > | Planning Time: 0.222 ms | > | Execution Time: 0.050 ms | > </pre> <!-- --> > -- 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 ; > > <pre> > | QUERY PLAN | > | :------------------------------------------------------------------------------------------------------------------------------------------------- | > | Nested Loop (cost=1.15..25.29 rows=1 width=96) (actual rows=1 loops=1) | > | -&gt; Nested Loop (cost=0.86..16.98 rows=1 width=68) (actual rows=1 loops=1) | > | -&gt; Nested Loop (cost=0.57..8.68 rows=1 width=40) (actual rows=1 loops=1) | > | -&gt; Limit (cost=0.29..0.36 rows=1 width=16) (actual rows=1 loops=1) | > | -&gt; Subquery Scan on q (cost=0.29..1488.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: 22 | > | -&gt; WindowAgg (cost=0.29..1288.29 rows=20000 width=112) (actual rows=23 loops=1) | > | -&gt; Index Scan Backward using uq_version on tab (cost=0.29..888.29 rows=20000 width=100) (actual rows=24 loops=1) | > | -&gt; 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) | > | -&gt; 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) | > | -&gt; 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.347 ms | > | Execution Time: 0.112 ms | > </pre> <!-- --> > -- 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 ; > > <pre> > | QUERY PLAN | > | :------------------------------------------------------------------------------------------------------------------------------ | > | HashAggregate (cost=5776.31..5796.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 (?)) | > | -&gt; WindowAgg (cost=5276.31..5626.31 rows=20000 width=108) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=5276.31..5326.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 | > | -&gt; WindowAgg (cost=3497.54..3847.54 rows=20000 width=172) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=3497.54..3547.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: 1735kB | > | -&gt; WindowAgg (cost=1718.77..2068.77 rows=20000 width=140) (actual rows=20000 loops=1) | > | -&gt; Sort (cost=1718.77..1768.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: 1723kB | > | -&gt; Seq Scan on tab (cost=0.00..290.00 rows=20000 width=108) (actual rows=20000 loops=1) | > | Planning Time: 0.086 ms | > | Execution Time: 49.105 ms | > </pre> <!-- --> > -- Gerard H. Pille > EXPLAIN (ANALYZE, TIMING OFF) > select * from test_get_lnn() ; > > <pre> > | 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.036 ms | > | Execution Time: 0.168 ms | > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=b8c636e62ab10e4800d59f829687b0ff)*
back to fiddle