clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805483 fiddles created (40786 in the last week).

CREATE TABLE tab ( version INT, -- no PRIMARY KEY, yet! 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); SELECT * FROM tab ORDER BY version;
5 rows affected
version col_a col_b col_c
1 A1 B1
2 A2 B2
3 A3 B3
4 A5 C1
5 A1
 hidden batch(es)


-- 1. Quassnoi Option 1 SELECT (SELECT col_a FROM tab WHERE col_a IS NOT NULL ORDER BY version DESC LIMIT 1) AS col_a, (SELECT col_b FROM tab WHERE col_b IS NOT NULL ORDER BY version DESC LIMIT 1) AS col_b, (SELECT col_c FROM tab WHERE col_c IS NOT NULL ORDER BY version DESC LIMIT 1) AS col_c;
col_a col_b col_c
A1 B3 C1
 hidden batch(es)


-- 2. 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
 hidden batch(es)


-- 3. 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
 hidden batch(es)


-- 4. Erwin SQL SELECT * FROM ( SELECT first_value(col_a) OVER (ORDER BY CASE WHEN col_a IS NULL THEN 0 ELSE version END DESC) AS col_a , first_value(col_b) OVER (ORDER BY CASE WHEN col_b IS NULL THEN 0 ELSE version END DESC) AS col_b , first_value(col_c) OVER (ORDER BY CASE WHEN col_c IS NULL THEN 0 ELSE version END DESC) AS col_c FROM tab ) sub LIMIT 1;
col_a col_b col_c
A1 B3 C1
 hidden batch(es)


-- 5. Gerard H. Pille 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; $$; select * from test_get_lnn();
col_a col_b col_c
A1 B3 C1
 hidden batch(es)


-- 6. Erwin func CREATE OR REPLACE FUNCTION func(OUT _col_a text, OUT _col_b text, OUT _col_c text) LANGUAGE plpgsql AS $func$ DECLARE _row record; BEGIN FOR _row IN SELECT col_a, col_b, col_c FROM tab ORDER BY version DESC LOOP IF _col_a IS NULL AND _row.col_a IS NOT NULL THEN _col_a := _row.col_a; END IF; IF _col_b IS NULL AND _row.col_b IS NOT NULL THEN _col_b := _row.col_b; END IF; IF _col_c IS NULL AND _row.col_c IS NOT NULL THEN _col_c := _row.col_c; END IF; EXIT WHEN (_col_a, _col_b, _col_c) IS NOT NULL; END LOOP; END $func$; select * from func();
_col_a _col_b _col_c
A1 B3 C1
 hidden batch(es)


-- 7. Erwin first() agg -- create custom aggregate function first() *ONCE* -- see: https://wiki.postgresql.org/wiki/First/last_(aggregate) CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement) RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS 'SELECT $1'; CREATE AGGREGATE public.first(anyelement) ( SFUNC = public.first_agg , STYPE = anyelement , PARALLEL = SAFE ); SELECT first(col_a) AS col_a , first(col_b) AS col_b , first(col_c) AS col_c FROM (SELECT col_a, col_b, col_c FROM tab ORDER BY version DESC) sub;
col_a col_b col_c
A1 B3 C1
 hidden batch(es)


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


ALTER TABLE tab ADD PRIMARY KEY (version);
 hidden batch(es)


VACUUM ANALYZE tab; -- !!!
 hidden batch(es)


-- 1.1. 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) AS col_a, (SELECT col_b FROM tab WHERE col_b IS NOT NULL ORDER BY version DESC LIMIT 1) AS col_b, (SELECT col_c FROM tab WHERE col_c IS NOT NULL ORDER BY version DESC LIMIT 1) AS col_c;
QUERY PLAN
Result (cost=1.42..1.43 rows=1 width=96) (actual rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.28..0.74 rows=1 width=6) (actual rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab (cost=0.28..43.27 rows=93 width=6) (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.36 rows=1 width=6) (actual rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab tab_1 (cost=0.28..43.27 rows=527 width=6) (actual rows=1 loops=1)
Filter: (col_b IS NOT NULL)
Rows Removed by Filter: 5
InitPlan 3 (returns $2)
-> Limit (cost=0.28..0.32 rows=1 width=6) (actual rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab tab_2 (cost=0.28..43.27 rows=906 width=6) (actual rows=1 loops=1)
Filter: (col_c IS NOT NULL)
Planning Time: 0.214 ms
Execution Time: 0.100 ms
 hidden batch(es)


-- 1.2. 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.28 rows=1 width=6) (actual rows=1 loops=1)
-> Nested Loop (cost=0.83..16.98 rows=1 width=8) (actual rows=1 loops=1)
-> Nested Loop (cost=0.55..8.67 rows=1 width=10) (actual rows=1 loops=1)
-> Limit (cost=0.28..0.35 rows=1 width=16) (actual rows=1 loops=1)
-> Subquery Scan on q (cost=0.28..73.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: 5
-> WindowAgg (cost=0.28..63.27 rows=1000 width=112) (actual rows=6 loops=1)
-> Index Scan Backward using tab_pkey on tab (cost=0.28..43.27 rows=1000 width=10) (actual rows=7 loops=1)
-> Index Scan using tab_pkey on tab a (cost=0.28..8.29 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_a)
-> Index Scan using tab_pkey on tab b (cost=0.28..8.29 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_b)
-> Index Scan using tab_pkey on tab c (cost=0.28..8.29 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_c)
Planning Time: 0.262 ms
Execution Time: 0.227 ms
 hidden batch(es)


-- 1.3. 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..225.52 rows=103 width=108) (actual rows=1 loops=1)
Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?))
Batches: 1 Memory Usage: 32kB
-> WindowAgg (cost=199.49..216.99 rows=1000 width=108) (actual rows=1000 loops=1)
-> Sort (cost=199.49..201.99 rows=1000 width=82) (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=82) (actual rows=1000 loops=1)
-> Sort (cost=132.16..134.66 rows=1000 width=50) (actual rows=1000 loops=1)
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC
Sort Method: quicksort Memory: 102kB
-> WindowAgg (cost=64.83..82.33 rows=1000 width=50) (actual rows=1000 loops=1)
-> Sort (cost=64.83..67.33 rows=1000 width=18) (actual rows=1000 loops=1)
Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC
Sort Method: quicksort Memory: 87kB
-> Seq Scan on tab (cost=0.00..15.00 rows=1000 width=18) (actual rows=1000 loops=1)
Planning Time: 0.075 ms
Execution Time: 3.182 ms
 hidden batch(es)


-- 1.4. Erwin SQL EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM ( SELECT first_value(col_a) OVER (ORDER BY CASE WHEN col_a IS NULL THEN 0 ELSE version END DESC) AS col_a , first_value(col_b) OVER (ORDER BY CASE WHEN col_b IS NULL THEN 0 ELSE version END DESC) AS col_b , first_value(col_c) OVER (ORDER BY CASE WHEN col_c IS NULL THEN 0 ELSE version END DESC) AS col_c FROM tab ) sub LIMIT 1;
QUERY PLAN
Limit (cost=199.49..199.51 rows=1 width=96) (actual rows=1 loops=1)
-> Subquery Scan on sub (cost=199.49..226.99 rows=1000 width=96) (actual rows=1 loops=1)
-> WindowAgg (cost=199.49..216.99 rows=1000 width=108) (actual rows=1 loops=1)
-> Sort (cost=199.49..201.99 rows=1000 width=82) (actual rows=2 loops=1)
Sort Key: (CASE WHEN (tab.col_a IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 103kB
-> WindowAgg (cost=132.16..149.66 rows=1000 width=82) (actual rows=1000 loops=1)
-> Sort (cost=132.16..134.66 rows=1000 width=50) (actual rows=1000 loops=1)
Sort Key: (CASE WHEN (tab.col_b IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 102kB
-> WindowAgg (cost=64.83..82.33 rows=1000 width=50) (actual rows=1000 loops=1)
-> Sort (cost=64.83..67.33 rows=1000 width=18) (actual rows=1000 loops=1)
Sort Key: (CASE WHEN (tab.col_c IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 87kB
-> Seq Scan on tab (cost=0.00..15.00 rows=1000 width=18) (actual rows=1000 loops=1)
Planning Time: 0.087 ms
Execution Time: 2.067 ms
 hidden batch(es)


-- 1.5. 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.015 ms
Execution Time: 0.184 ms
 hidden batch(es)


-- 1.6. Erwin func EXPLAIN (ANALYZE, TIMING OFF) select * from func();
QUERY PLAN
Function Scan on func (cost=0.25..0.26 rows=1 width=96) (actual rows=1 loops=1)
Planning Time: 0.013 ms
Execution Time: 0.121 ms
 hidden batch(es)


-- 1.7. Erwin first() agg EXPLAIN ANALYZE SELECT first(col_a) AS col_a , first(col_b) AS col_b , first(col_c) AS col_c FROM (SELECT col_a, col_b, col_c FROM tab ORDER BY version DESC) sub;
QUERY PLAN
Aggregate (cost=803.27..803.28 rows=1 width=96) (actual time=3.172..3.172 rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab (cost=0.28..43.27 rows=1000 width=10) (actual time=0.007..0.451 rows=1000 loops=1)
Planning Time: 0.068 ms
Execution Time: 3.190 ms
 hidden batch(es)


-- adding 20k rows with nulls insert into tab (version, col_a, col_b, col_c) select g, null, null, null from generate_series(1001, 20000) g;
19000 rows affected
 hidden batch(es)


VACUUM ANALYZE tab; -- !
 hidden batch(es)


-- 2.1. 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) AS col_a, (SELECT col_b FROM tab WHERE col_b IS NOT NULL ORDER BY version DESC LIMIT 1) AS col_b, (SELECT col_c FROM tab WHERE col_c IS NOT NULL ORDER BY version DESC LIMIT 1) AS col_c;
QUERY PLAN
Result (cost=9.39..9.40 rows=1 width=96) (actual rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.29..6.95 rows=1 width=6) (actual rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab (cost=0.29..620.29 rows=93 width=6) (actual rows=1 loops=1)
Filter: (col_a IS NOT NULL)
Rows Removed by Filter: 19002
InitPlan 2 (returns $1)
-> Limit (cost=0.29..1.46 rows=1 width=6) (actual rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab tab_1 (cost=0.29..620.29 rows=527 width=6) (actual rows=1 loops=1)
Filter: (col_b IS NOT NULL)
Rows Removed by Filter: 19005
InitPlan 3 (returns $2)
-> Limit (cost=0.29..0.97 rows=1 width=6) (actual rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab tab_2 (cost=0.29..620.29 rows=906 width=6) (actual rows=1 loops=1)
Filter: (col_c IS NOT NULL)
Rows Removed by Filter: 19000
Planning Time: 0.263 ms
Execution Time: 7.698 ms
 hidden batch(es)


-- 2.2. 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.28 rows=1 width=6) (actual rows=1 loops=1)
-> Nested Loop (cost=0.86..16.97 rows=1 width=8) (actual rows=1 loops=1)
-> Nested Loop (cost=0.57..8.67 rows=1 width=10) (actual rows=1 loops=1)
-> Limit (cost=0.29..0.35 rows=1 width=16) (actual rows=1 loops=1)
-> Subquery Scan on q (cost=0.29..1220.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: 19005
-> WindowAgg (cost=0.29..1020.29 rows=20000 width=112) (actual rows=19006 loops=1)
-> Index Scan Backward using tab_pkey on tab (cost=0.29..620.29 rows=20000 width=10) (actual rows=19007 loops=1)
-> Index Scan using tab_pkey on tab a (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_a)
-> Index Scan using tab_pkey on tab b (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_b)
-> Index Scan using tab_pkey on tab c (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_c)
Planning Time: 0.272 ms
Execution Time: 12.246 ms
 hidden batch(es)


-- 2.3. 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 (?))
Batches: 1 Memory Usage: 121kB
-> WindowAgg (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=20000 loops=1)
-> Sort (cost=5275.31..5325.31 rows=20000 width=82) (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=82) (actual rows=20000 loops=1)
-> Sort (cost=3496.54..3546.54 rows=20000 width=50) (actual rows=20000 loops=1)
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC
Sort Method: quicksort Memory: 1736kB
-> WindowAgg (cost=1717.77..2067.77 rows=20000 width=50) (actual rows=20000 loops=1)
-> Sort (cost=1717.77..1767.77 rows=20000 width=18) (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..289.00 rows=20000 width=18) (actual rows=20000 loops=1)
Planning Time: 0.067 ms
Execution Time: 51.139 ms
 hidden batch(es)


-- 2.4. Erwin SQL EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM ( SELECT first_value(col_a) OVER (ORDER BY CASE WHEN col_a IS NULL THEN 0 ELSE version END DESC) AS col_a , first_value(col_b) OVER (ORDER BY CASE WHEN col_b IS NULL THEN 0 ELSE version END DESC) AS col_b , first_value(col_c) OVER (ORDER BY CASE WHEN col_c IS NULL THEN 0 ELSE version END DESC) AS col_c FROM tab ) sub LIMIT 1;
QUERY PLAN
Limit (cost=5275.31..5275.34 rows=1 width=96) (actual rows=1 loops=1)
-> Subquery Scan on sub (cost=5275.31..5825.31 rows=20000 width=96) (actual rows=1 loops=1)
-> WindowAgg (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=1 loops=1)
-> Sort (cost=5275.31..5325.31 rows=20000 width=82) (actual rows=2 loops=1)
Sort Key: (CASE WHEN (tab.col_a IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 2331kB
-> WindowAgg (cost=3496.54..3846.54 rows=20000 width=82) (actual rows=20000 loops=1)
-> Sort (cost=3496.54..3546.54 rows=20000 width=50) (actual rows=20000 loops=1)
Sort Key: (CASE WHEN (tab.col_b IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 1736kB
-> WindowAgg (cost=1717.77..2067.77 rows=20000 width=50) (actual rows=20000 loops=1)
-> Sort (cost=1717.77..1767.77 rows=20000 width=18) (actual rows=20000 loops=1)
Sort Key: (CASE WHEN (tab.col_c IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 1722kB
-> Seq Scan on tab (cost=0.00..289.00 rows=20000 width=18) (actual rows=20000 loops=1)
Planning Time: 0.115 ms
Execution Time: 33.901 ms
 hidden batch(es)


-- 2.5. 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.034 ms
Execution Time: 15.330 ms
 hidden batch(es)


-- 2.6. Erwin func EXPLAIN (ANALYZE, TIMING OFF) select * from func();
QUERY PLAN
Function Scan on func (cost=0.25..0.26 rows=1 width=96) (actual rows=1 loops=1)
Planning Time: 0.014 ms
Execution Time: 11.338 ms
 hidden batch(es)


-- 2.7. Erwin first() agg EXPLAIN ANALYZE SELECT first(col_a) AS col_a , first(col_b) AS col_b , first(col_c) AS col_c FROM (SELECT col_a, col_b, col_c FROM tab ORDER BY version DESC) sub;
QUERY PLAN
Aggregate (cost=15820.29..15820.30 rows=1 width=96) (actual time=7.671..7.672 rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab (cost=0.29..620.29 rows=20000 width=10) (actual time=0.008..3.797 rows=20000 loops=1)
Planning Time: 0.061 ms
Execution Time: 7.692 ms
 hidden batch(es)


-- Add partial indexex: (version) INCLUDE (column) WHERE (column IS NOT NULL) create index ix_version_include_a on tab (version) INCLUDE (col_a) WHERE (col_a IS NOT NULL); create index ix_version_include_b on tab (version) INCLUDE (col_b) WHERE (col_b IS NOT NULL); create index ix_version_include_c on tab (version) INCLUDE (col_c) WHERE (col_c IS NOT NULL);
 hidden batch(es)


VACUUM ANALYZE tab; -- !
 hidden batch(es)


-- 3.1. 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) AS col_a, (SELECT col_b FROM tab WHERE col_b IS NOT NULL ORDER BY version DESC LIMIT 1) AS col_b, (SELECT col_c FROM tab WHERE col_c IS NOT NULL ORDER BY version DESC LIMIT 1) AS col_c;
QUERY PLAN
Result (cost=0.88..0.89 rows=1 width=96) (actual rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.14..0.24 rows=1 width=6) (actual rows=1 loops=1)
-> Index Only Scan Backward using ix_version_include_a on tab (cost=0.14..9.54 rows=93 width=6) (actual rows=1 loops=1)
Heap Fetches: 0
InitPlan 2 (returns $1)
-> Limit (cost=0.28..0.32 rows=1 width=6) (actual rows=1 loops=1)
-> Index Only Scan Backward using ix_version_include_b on tab tab_1 (cost=0.28..24.18 rows=527 width=6) (actual rows=1 loops=1)
Heap Fetches: 0
InitPlan 3 (returns $2)
-> Limit (cost=0.28..0.31 rows=1 width=6) (actual rows=1 loops=1)
-> Index Only Scan Backward using ix_version_include_c on tab tab_2 (cost=0.28..33.87 rows=906 width=6) (actual rows=1 loops=1)
Heap Fetches: 0
Planning Time: 0.363 ms
Execution Time: 0.116 ms
 hidden batch(es)


-- 3.2. 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.28 rows=1 width=6) (actual rows=1 loops=1)
-> Nested Loop (cost=0.86..16.97 rows=1 width=8) (actual rows=1 loops=1)
-> Nested Loop (cost=0.57..8.67 rows=1 width=10) (actual rows=1 loops=1)
-> Limit (cost=0.29..0.35 rows=1 width=16) (actual rows=1 loops=1)
-> Subquery Scan on q (cost=0.29..1220.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: 19005
-> WindowAgg (cost=0.29..1020.29 rows=20000 width=112) (actual rows=19006 loops=1)
-> Index Scan Backward using tab_pkey on tab (cost=0.29..620.29 rows=20000 width=10) (actual rows=19007 loops=1)
-> Index Scan using tab_pkey on tab a (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_a)
-> Index Scan using tab_pkey on tab b (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_b)
-> Index Scan using tab_pkey on tab c (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_c)
Planning Time: 0.296 ms
Execution Time: 12.278 ms
 hidden batch(es)


-- 3.3. 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 (?))
Batches: 1 Memory Usage: 121kB
-> WindowAgg (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=20000 loops=1)
-> Sort (cost=5275.31..5325.31 rows=20000 width=82) (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=82) (actual rows=20000 loops=1)
-> Sort (cost=3496.54..3546.54 rows=20000 width=50) (actual rows=20000 loops=1)
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC
Sort Method: quicksort Memory: 1736kB
-> WindowAgg (cost=1717.77..2067.77 rows=20000 width=50) (actual rows=20000 loops=1)
-> Sort (cost=1717.77..1767.77 rows=20000 width=18) (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..289.00 rows=20000 width=18) (actual rows=20000 loops=1)
Planning Time: 0.087 ms
Execution Time: 49.789 ms
 hidden batch(es)


-- 3.4. Erwin SQL EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM ( SELECT first_value(col_a) OVER (ORDER BY CASE WHEN col_a IS NULL THEN 0 ELSE version END DESC) AS col_a , first_value(col_b) OVER (ORDER BY CASE WHEN col_b IS NULL THEN 0 ELSE version END DESC) AS col_b , first_value(col_c) OVER (ORDER BY CASE WHEN col_c IS NULL THEN 0 ELSE version END DESC) AS col_c FROM tab ) sub LIMIT 1;
QUERY PLAN
Limit (cost=5275.31..5275.34 rows=1 width=96) (actual rows=1 loops=1)
-> Subquery Scan on sub (cost=5275.31..5825.31 rows=20000 width=96) (actual rows=1 loops=1)
-> WindowAgg (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=1 loops=1)
-> Sort (cost=5275.31..5325.31 rows=20000 width=82) (actual rows=2 loops=1)
Sort Key: (CASE WHEN (tab.col_a IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 2331kB
-> WindowAgg (cost=3496.54..3846.54 rows=20000 width=82) (actual rows=20000 loops=1)
-> Sort (cost=3496.54..3546.54 rows=20000 width=50) (actual rows=20000 loops=1)
Sort Key: (CASE WHEN (tab.col_b IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 1736kB
-> WindowAgg (cost=1717.77..2067.77 rows=20000 width=50) (actual rows=20000 loops=1)
-> Sort (cost=1717.77..1767.77 rows=20000 width=18) (actual rows=20000 loops=1)
Sort Key: (CASE WHEN (tab.col_c IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 1722kB
-> Seq Scan on tab (cost=0.00..289.00 rows=20000 width=18) (actual rows=20000 loops=1)
Planning Time: 0.131 ms
Execution Time: 33.245 ms
 hidden batch(es)


-- 3.5. 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.034 ms
Execution Time: 15.457 ms
 hidden batch(es)


-- 3.6. Erwin func EXPLAIN (ANALYZE, TIMING OFF) select * from func();
QUERY PLAN
Function Scan on func (cost=0.25..0.26 rows=1 width=96) (actual rows=1 loops=1)
Planning Time: 0.016 ms
Execution Time: 11.441 ms
 hidden batch(es)


-- 3.7. Erwin first() agg EXPLAIN ANALYZE SELECT first(col_a) AS col_a , first(col_b) AS col_b , first(col_c) AS col_c FROM (SELECT col_a, col_b, col_c FROM tab ORDER BY version DESC) sub;
QUERY PLAN
Aggregate (cost=15820.29..15820.30 rows=1 width=96) (actual time=7.614..7.614 rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab (cost=0.29..620.29 rows=20000 width=10) (actual time=0.010..3.823 rows=20000 loops=1)
Planning Time: 0.095 ms
Execution Time: 7.636 ms
 hidden batch(es)


-- 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.5 then (random()*100)::int end, col_c = case when random() < 0.9 then (random()*100)::int end from generate_series(19900, 20000) g where tab.version = g;
101 rows affected
 hidden batch(es)


VACUUM ANALYZE tab;
 hidden batch(es)


-- 4.1. 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) AS col_a, (SELECT col_b FROM tab WHERE col_b IS NOT NULL ORDER BY version DESC LIMIT 1) AS col_b, (SELECT col_c FROM tab WHERE col_c IS NOT NULL ORDER BY version DESC LIMIT 1) AS col_c;
QUERY PLAN
Result (cost=0.86..0.87 rows=1 width=96) (actual rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.14..0.24 rows=1 width=6) (actual rows=1 loops=1)
-> Index Only Scan Backward using ix_version_include_a on tab (cost=0.14..9.67 rows=102 width=6) (actual rows=1 loops=1)
Heap Fetches: 0
InitPlan 2 (returns $1)
-> Limit (cost=0.28..0.32 rows=1 width=6) (actual rows=1 loops=1)
-> Index Only Scan Backward using ix_version_include_b on tab tab_1 (cost=0.28..24.91 rows=576 width=6) (actual rows=1 loops=1)
Heap Fetches: 0
InitPlan 3 (returns $2)
-> Limit (cost=0.28..0.31 rows=1 width=6) (actual rows=1 loops=1)
-> Index Only Scan Backward using ix_version_include_c on tab tab_2 (cost=0.28..35.24 rows=998 width=6) (actual rows=1 loops=1)
Heap Fetches: 0
Planning Time: 0.316 ms
Execution Time: 0.066 ms
 hidden batch(es)


-- 4.2. 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.28 rows=1 width=6) (actual rows=1 loops=1)
-> Nested Loop (cost=0.86..16.97 rows=1 width=8) (actual rows=1 loops=1)
-> Nested Loop (cost=0.57..8.67 rows=1 width=10) (actual rows=1 loops=1)
-> Limit (cost=0.29..0.35 rows=1 width=16) (actual rows=1 loops=1)
-> Subquery Scan on q (cost=0.29..1221.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: 4
-> WindowAgg (cost=0.29..1021.29 rows=20000 width=112) (actual rows=5 loops=1)
-> Index Scan Backward using tab_pkey on tab (cost=0.29..621.29 rows=20000 width=10) (actual rows=6 loops=1)
-> Index Scan using tab_pkey on tab a (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_a)
-> Index Scan using tab_pkey on tab b (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_b)
-> Index Scan using tab_pkey on tab c (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1)
Index Cond: (version = q.last_c)
Planning Time: 0.274 ms
Execution Time: 0.073 ms
 hidden batch(es)


-- 4.3. 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=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 (?))
Batches: 1 Memory Usage: 121kB
-> WindowAgg (cost=5276.31..5626.31 rows=20000 width=108) (actual rows=20000 loops=1)
-> Sort (cost=5276.31..5326.31 rows=20000 width=82) (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=82) (actual rows=20000 loops=1)
-> Sort (cost=3497.54..3547.54 rows=20000 width=50) (actual rows=20000 loops=1)
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC
Sort Method: quicksort Memory: 1739kB
-> WindowAgg (cost=1718.77..2068.77 rows=20000 width=50) (actual rows=20000 loops=1)
-> Sort (cost=1718.77..1768.77 rows=20000 width=18) (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=18) (actual rows=20000 loops=1)
Planning Time: 0.073 ms
Execution Time: 49.209 ms
 hidden batch(es)


-- 4.4. Erwin SQL EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM ( SELECT first_value(col_a) OVER (ORDER BY CASE WHEN col_a IS NULL THEN 0 ELSE version END DESC) AS col_a , first_value(col_b) OVER (ORDER BY CASE WHEN col_b IS NULL THEN 0 ELSE version END DESC) AS col_b , first_value(col_c) OVER (ORDER BY CASE WHEN col_c IS NULL THEN 0 ELSE version END DESC) AS col_c FROM tab ) sub LIMIT 1;
QUERY PLAN
Limit (cost=5276.31..5276.34 rows=1 width=96) (actual rows=1 loops=1)
-> Subquery Scan on sub (cost=5276.31..5826.31 rows=20000 width=96) (actual rows=1 loops=1)
-> WindowAgg (cost=5276.31..5626.31 rows=20000 width=108) (actual rows=1 loops=1)
-> Sort (cost=5276.31..5326.31 rows=20000 width=82) (actual rows=2 loops=1)
Sort Key: (CASE WHEN (tab.col_a IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 2331kB
-> WindowAgg (cost=3497.54..3847.54 rows=20000 width=82) (actual rows=20000 loops=1)
-> Sort (cost=3497.54..3547.54 rows=20000 width=50) (actual rows=20000 loops=1)
Sort Key: (CASE WHEN (tab.col_b IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 1739kB
-> WindowAgg (cost=1718.77..2068.77 rows=20000 width=50) (actual rows=20000 loops=1)
-> Sort (cost=1718.77..1768.77 rows=20000 width=18) (actual rows=20000 loops=1)
Sort Key: (CASE WHEN (tab.col_c IS NULL) THEN 0 ELSE tab.version END) DESC
Sort Method: quicksort Memory: 1723kB
-> Seq Scan on tab (cost=0.00..290.00 rows=20000 width=18) (actual rows=20000 loops=1)
Planning Time: 0.129 ms
Execution Time: 33.573 ms
 hidden batch(es)


-- 4.5. 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.034 ms
Execution Time: 0.210 ms
 hidden batch(es)


-- 4.6. Erwin func EXPLAIN (ANALYZE, TIMING OFF) select * from func();
QUERY PLAN
Function Scan on func (cost=0.25..0.26 rows=1 width=96) (actual rows=1 loops=1)
Planning Time: 0.012 ms
Execution Time: 0.123 ms
 hidden batch(es)


-- 4.7. Erwin first() agg EXPLAIN ANALYZE SELECT first(col_a) AS col_a , first(col_b) AS col_b , first(col_c) AS col_c FROM (SELECT col_a, col_b, col_c FROM tab ORDER BY version DESC) sub;
QUERY PLAN
Aggregate (cost=15821.29..15821.30 rows=1 width=96) (actual time=7.941..7.941 rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab (cost=0.29..621.29 rows=20000 width=10) (actual time=0.008..3.880 rows=20000 loops=1)
Planning Time: 0.066 ms
Execution Time: 7.962 ms
 hidden batch(es)


-- Run functions a coule of times to level caching effects SELECT func(), test_get_lnn() FROM generate_series (1, 12) g
func test_get_lnn
(86,10,42) (86,10,42)
(86,10,42) (86,10,42)
(86,10,42) (86,10,42)
(86,10,42) (86,10,42)
(86,10,42) (86,10,42)
(86,10,42) (86,10,42)
(86,10,42) (86,10,42)
(86,10,42) (86,10,42)
(86,10,42) (86,10,42)
(86,10,42) (86,10,42)
(86,10,42) (86,10,42)
(86,10,42) (86,10,42)
 hidden batch(es)


-- x.5. 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.014 ms
Execution Time: 0.052 ms
 hidden batch(es)


-- x.6. Erwin func EXPLAIN (ANALYZE, TIMING OFF) select * from func();
QUERY PLAN
Function Scan on func (cost=0.25..0.26 rows=1 width=96) (actual rows=1 loops=1)
Planning Time: 0.012 ms
Execution Time: 0.050 ms
 hidden batch(es)