Postgres 15

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 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE t_test(a int, b int);
INSERT INTO t_test(a, b)
VALUES (1, 2),
       (3, NULL);

CREATE TABLE



INSERT 0 2


SELECT t AS typed, ROW(a, b) AS constructor, (SELECT ROW(a, b)) AS subquery,
       t = t AS "typed = typed", 
       t = ROW(a, b) AS "typed = constructor", 
       t = (SELECT ROW(a, b)) AS "typed = subquery", 
       ROW(a, b) = t AS "constructor = typed",
       ROW(a, b) = ROW(a, b) AS "constructor = constructor",
--        ROW(a, b) = (SELECT ROW(a, b)) AS "constructor = subquery", -- Also, WTF is this?!
       (SELECT ROW(a, b)) = t AS "subquery = typed",
       (SELECT ROW(a, b)) = ROW(a, b) AS "subquery = constructor",
       (SELECT ROW(a, b)) = (SELECT ROW(a, b)) AS "subquery = subquery"
  FROM t_test AS t;


typed
constructor
subquery
typed = typed
typed = constructor
typed = subquery
constructor = typed
constructor = constructor
subquery = typed
subquery = constructor
subquery = subquery


(1,2)
(1,2)
(1,2)
t
t
t
t
t
t
t
t

(3,)
(3,)
(3,)
t
t
t
t
null
t
t
t



SELECT 2


SELECT (SELECT ROW(a, b)) = ROW(a, b) AS "subquery = constructor" -- This works
  FROM t_test AS t;


subquery = constructor


t

t



SELECT 2


SELECT ROW(a, b) = (SELECT ROW(a, b)) AS "constructor = subquery" -- But WTH is this?!
  FROM t_test AS t;

ERROR:  subquery has too few columns
LINE 1: SELECT ROW(a, b) = (SELECT ROW(a, b)) AS "constructor = subq...
                         ^