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)
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
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.
CREATE OR REPLACE FUNCTION func(OUT _col_a text, OUT _col_b text, OUT _col_c text)
LANGUAGE plpgsql AS
FOR _row IN
SELECT col_a, col_b, col_c FROM tbl ORDER BY version DESC
IF _col_a IS NULL AND _row.col_a IS NOT NULL THEN
_col_a := _row.col_a;
IF _col_b IS NULL AND _row.col_b IS NOT NULL THEN
_col_b := _row.col_b;
IF _col_c IS NULL AND _row.col_c IS NOT NULL THEN
_col_c := _row.col_c;
EXIT WHEN (_col_a, _col_b, _col_c) IS NOT NULL;
SELECT * FROM func();
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 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
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
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.