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) |
> | -> 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) |
> | 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: 2 |
> | 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) |
> | 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) |
> | -> 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: 2 |
> | -> WindowAgg (cost=0.28..75.28 rows=1000 width=112) (actual rows=3 loops=1) |
> | -> Index Scan Backward using uq_version on tab (cost=0.28..55.27 rows=1000 width=100) (actual rows=4 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.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 (?)) |
> | -> 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: 88kB |
> | -> 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) |
> | -> 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..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) |
> | -> 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..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) |
> | -> 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..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) |
> | -> 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..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 |
> | -> WindowAgg (cost=0.29..1218.29 rows=18000 width=112) (actual rows=19003 loops=1) |
> | -> Index Scan Backward using uq_version on tab (cost=0.29..858.29 rows=18000 width=100) (actual rows=19004 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.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 (?)) |
> | -> WindowAgg (cost=4716.64..5031.64 rows=18000 width=108) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> WindowAgg (cost=3129.43..3444.43 rows=18000 width=172) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> WindowAgg (cost=1542.21..1857.21 rows=18000 width=140) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> 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) |
> | -> Limit (cost=0.28..0.30 rows=1 width=36) (actual rows=1 loops=1) |
> | -> 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) |
> | -> Limit (cost=0.28..0.30 rows=1 width=36) (actual rows=1 loops=1) |
> | -> 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) |
> | -> 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..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) |
> | -> 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..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 |
> | -> WindowAgg (cost=0.29..1288.29 rows=20000 width=112) (actual rows=19003 loops=1) |
> | -> Index Scan Backward using uq_version on tab (cost=0.29..888.29 rows=20000 width=100) (actual rows=19004 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.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 (?)) |
> | -> WindowAgg (cost=5276.31..5626.31 rows=20000 width=108) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> WindowAgg (cost=3497.54..3847.54 rows=20000 width=172) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> WindowAgg (cost=1718.77..2068.77 rows=20000 width=140) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> 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) |
> | -> Limit (cost=0.28..0.30 rows=1 width=36) (actual rows=1 loops=1) |
> | -> 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) |
> | -> Limit (cost=0.28..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.28..577.84 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..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) |
> | -> 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..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 |
> | -> WindowAgg (cost=0.29..1288.29 rows=20000 width=112) (actual rows=19003 loops=1) |
> | -> Index Scan Backward using uq_version on tab (cost=0.29..888.29 rows=20000 width=100) (actual rows=19004 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.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 (?)) |
> | -> WindowAgg (cost=5276.31..5626.31 rows=20000 width=108) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> WindowAgg (cost=3497.54..3847.54 rows=20000 width=172) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> WindowAgg (cost=1718.77..2068.77 rows=20000 width=140) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> 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) |
> | -> Limit (cost=0.28..0.30 rows=1 width=36) (actual rows=1 loops=1) |
> | -> 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) |
> | -> Limit (cost=0.28..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.28..577.84 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..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) |
> | -> 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..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 |
> | -> WindowAgg (cost=0.29..1288.29 rows=20000 width=112) (actual rows=23 loops=1) |
> | -> Index Scan Backward using uq_version on tab (cost=0.29..888.29 rows=20000 width=100) (actual rows=24 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.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 (?)) |
> | -> WindowAgg (cost=5276.31..5626.31 rows=20000 width=108) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> WindowAgg (cost=3497.54..3847.54 rows=20000 width=172) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> WindowAgg (cost=1718.77..2068.77 rows=20000 width=140) (actual rows=20000 loops=1) |
> | -> 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 |
> | -> 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