first() aggregate function
This table is not going to be huge as in this will be a couple of thousand rows only. So, not worried very much about performance.
I suppose the custom aggregate function first() will be the simplest solution, then.
Install the custom aggregate function first() as instructed in the Postgres Wiki. (I updated the Wiki page while being at it.)
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
);
Then this can be your query:
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;
Fast and simple for small tables. But does not scale well, so can't keep up with some of the optimized solutions for bigger tables.
PL/pgSQL function
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 tbl 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$;
Call:
SELECT * FROM func();
db<>fiddle here
Note that the expression (_col_a, _col_b, _col_c) IS NOT NULL is only true if all columns are NOT NULL. See:
The point is to scan the table once, and only until we find values, instead of multiple times and/or the complete table. The function adds considerable overhead. This approach looks better the more columns we process at once, the sooner we find the last missing value, and the wider the underlying table row.
This is much like the Gerard earlier function, but works with a single assignment per output columns, which should make a substantial difference as assignments are comparatively expensive in PL/pgSQL.
Index for Quassnoi's option 1
If performance is the paramount requirement (which it is not for the OP, but for our little performance bet), then Quassnoi's option 1 coupled with partial indexes should perform best:
CREATE INDEX ON aang (version DESC) INCLUDE (col_a) WHERE col_a IS NOT NULL;
CREATE INDEX ON aang (version DESC) INCLUDE (col_b) WHERE col_b IS NOT NULL;
CREATE INDEX ON aang (version DESC) INCLUDE (col_c) WHERE col_c IS NOT NULL;
Reduces the query plan to three perfectly fast index-only scans. But with only very few rows to traverse, the overhead weighs in, and the functions are still faster in a quick test.
first_value() with LIMIT 1
J.D.'s query with first_value() is much faster with LIMIT 1 instead of DISTINCT. Scales much better, as the cost of DISTINCT grows with O(N²), while the cost of LIMIT 1 is constant.
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 tbl
) sub
LIMIT 1;
Big benchmark roundup
Based on Vérace's fiddle (kudos!), with major changes:
- Postgres 14 instead of Postgres 12
- add VACUUM ANALYZE
- drop redundant UNIQUE index
- trim a lot of noise
- add my additional solutions
- drop the run with partial, non-covering indexes (little added value)
- add numbers to guide through the many tests
db<>fiddle here
Quassnoi's option 1 and the functions dominate.
At the bottom I run the functions a couple of times to level caching effects. After that, my function is only little faster than Gerard's, - as only few rows have to be processed in that test.
