-- 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
A5
B2
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
A5
B2
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 ;
-- 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.72..0.73 rows=1 width=96) (actual rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab tab_2 (cost=0.15..57.60 rows=627 width=36) (actual rows=1 loops=1)
Filter: (col_c IS NOT NULL)
Rows Removed by Filter: 1
Planning Time: 0.114 ms
Execution Time: 0.030 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 using tab_pkey on tab a (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1)
Index Cond: (version = q.last_a)
-> Index Scan using tab_pkey on tab b (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1)
Index Cond: (version = q.last_b)
-> Index Scan using tab_pkey on tab c (cost=0.15..8.17 rows=1 width=36) (actual rows=1 loops=1)
Index Cond: (version = q.last_c)
Planning Time: 0.279 ms
Execution Time: 0.174 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 ;
-- adding 20k rows with nulls
insert into tab (version, col_A, col_B, col_C)
select i, null, null, null
from generate_series(6,20000) as g(i) ;
19995 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.21..1.22 rows=1 width=96) (actual rows=1 loops=1)
-> Index Scan Backward using tab_pkey on tab tab_2 (cost=0.28..668.39 rows=5579 width=36) (actual rows=1 loops=1)
Filter: (col_c IS NOT NULL)
Rows Removed by Filter: 19996
Planning Time: 0.189 ms
Execution Time: 8.558 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 using tab_pkey on tab a (cost=0.28..8.30 rows=1 width=36) (actual rows=1 loops=1)
Index Cond: (version = q.last_a)
-> Index Scan using tab_pkey on tab b (cost=0.28..8.30 rows=1 width=36) (actual rows=1 loops=1)
Index Cond: (version = q.last_b)
-> Index Scan using tab_pkey on tab c (cost=0.28..8.30 rows=1 width=36) (actual rows=1 loops=1)
Index Cond: (version = q.last_c)
Planning Time: 0.245 ms
Execution Time: 15.852 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 ;
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)
…
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.47..0.48 rows=1 width=96) (actual rows=1 loops=1)
-> Index Scan Backward using ix_vc on tab tab_2 (cost=0.12..559.13 rows=19900 width=36) (actual rows=1 loops=1)
Planning Time: 0.625 ms
Execution Time: 0.108 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 tab_pkey on tab (cost=0.29..884.29 rows=20000 width=100) (actual rows=19999 loops=1)
-> Index Scan using tab_pkey 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 tab_pkey 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 tab_pkey 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.417 ms
Execution Time: 18.640 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 ;
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)
…
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.47..0.48 rows=1 width=96) (actual rows=1 loops=1)
-> Index Only Scan Backward using ix_version_include_c on tab tab_2 (cost=0.12..559.13 rows=19900 width=36) (actual rows=1 loops=1)
Heap Fetches: 1
Planning Time: 0.466 ms
Execution Time: 0.086 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 tab_pkey on tab (cost=0.29..884.29 rows=20000 width=100) (actual rows=19999 loops=1)
-> Index Scan using tab_pkey 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 tab_pkey 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 tab_pkey 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.304 ms
Execution Time: 12.638 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 ;
