-- 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
) ;
col_a
col_b
col_c
A1
B3
C1
…
col_a
col_b
col_c
A1
B3
C1
…
hidden batch(es)
-- 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
…
col_a
col_b
col_c
A1
B3
C1
…
hidden batch(es)
-- 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
…
lasta
lastb
lastc
A1
B3
C1
…
hidden batch(es)
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() ;
✓
col_a
col_b
col_c
A1
B3
C1
…
✓
col_a
col_b
col_c
A1
B3
C1
…
hidden batch(es)
-- Gerard H. Pille
select * from test_get_lnn() ;
col_a
col_b
col_c
A1
B3
C1
…
col_a
col_b
col_c
A1
B3
C1
…
hidden batch(es)
-- 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) ;
-- 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
) ;
QUERY PLAN
Result (cost=0.99..1.00 rows=1 width=96) (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
…
hidden batch(es)
-- 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
-> 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
…
hidden batch(es)
-- 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 ;
-- 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.174 ms
…
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
…
hidden batch(es)
-- 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) ;
19000 rows affected
19000 rows affected
hidden batch(es)
-- 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
) ;
QUERY PLAN
Result (cost=1.01..1.02 rows=1 width=96) (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
…
hidden batch(es)
-- 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 ;
-> 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
…
hidden batch(es)
-- 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 ;
-- 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.037 ms
Execution Time: 37.447 ms
…
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
…
hidden batch(es)
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)'
✓
✓
✓
?column?
Adding partial indexes: (version) WHERE (column IS NOT NULL)
…
✓
✓
✓
?column?
Adding partial indexes: (version) WHERE (column IS NOT NULL)
…
hidden batch(es)
-- 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
) ;
QUERY PLAN
Result (cost=0.91..0.92 rows=1 width=96) (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
…
hidden batch(es)
-- 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 ;
-> 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
…
hidden batch(es)
-- 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 ;
-- 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.036 ms
Execution Time: 36.740 ms
…
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
…
hidden batch(es)
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)'
✓
✓
✓
?column?
Adding partial indexex: (version) INCLUDE (column) WHERE (column IS NOT NULL)
…
✓
✓
✓
?column?
Adding partial indexex: (version) INCLUDE (column) WHERE (column IS NOT NULL)
…
hidden batch(es)
-- 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
) ;
QUERY PLAN
Result (cost=0.91..0.92 rows=1 width=96) (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
…
hidden batch(es)
-- 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 ;
-> 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
…
hidden batch(es)
-- 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 ;
-- 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.036 ms
Execution Time: 37.143 ms
…
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
…
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.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 ;
101 rows affected
101 rows affected
hidden batch(es)
-- 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
) ;
QUERY PLAN
Result (cost=0.91..0.92 rows=1 width=96) (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
…
hidden batch(es)
-- 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 ;
-> 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
…
hidden batch(es)
-- 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 ;
