Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();


version


PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


CREATE TABLE tab
(
  col_A TEXT,
  col_B TEXT,
  col_C TEXT
);
INSERT INTO tab (col_A, col_B, col_C)
VALUES
('A1', 'B1', null),
('A2', 'B2', null),
('A3', 'B3', null),
('A4', null, 'C1'),
('A5', null, null);
5 rows affected

SELECT 
  *
FROM tab 
ORDER BY col_A, col_B;


col_a
col_b
col_c


A1
B1
null

A2
B2
null

A3
B3
null

A4
null
C1

A5
null
null


WITH x AS
(SELECT MAX(col_A) AS a FROM tab),
y AS 
(SELECT MAX(col_B) AS b FROM tab),
z AS
(SELECT MAX(col_C) AS c FROM tab)
SELECT x.a, y.b, z.c FROM x, y, z;


a
b
c


A5
B3
C1


WITH x AS
(SELECT COALESCE(MAX(col_A), '--') AS a FROM tab),
y AS 
(SELECT COALESCE(MAX(col_B), '--') AS b FROM tab),
z AS
(SELECT COALESCE(MAX(col_C), '--') AS c FROM tab)
SELECT x.a, y.b, z.c FROM x, y, z;


a
b
c


A5
B3
C1

col_a	col_b	col_c
A1	B1	null
A2	B2	null
A3	B3	null
A4	null	C1
A5	null	null