Postgres 16

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?.

CREATE TABLE foo (fooid int, foosubid int, fooname text);

INSERT INTO foo VALUES
  (1, 1, 'foo')
, (1, 1, 'foo')  -- dupe
, (1, 2, 'foo2')
, (2, 3, 'bar')
  
, (NULL, 4, 'null1')  -- null!
, (5, NULL, 'null2')
, (NULL, NULL, 'null3')
;

CREATE TABLE



INSERT 0 7


CREATE FUNCTION getfoo(int)
  RETURNS SETOF foo
   LANGUAGE sql AS
$$
SELECT * FROM foo WHERE fooid = $1;
$$;

CREATE FUNCTION


SELECT * FROM foo
WHERE foosubid IN (
                  SELECT foosubid
                  FROM   getfoo(foo.fooid) z
                  WHERE  z.fooid = foo.fooid
                  );


fooid
foosubid
fooname


1
1
foo

1
1
foo

1
2
foo2

2
3
bar



SELECT 4


SELECT * FROM foo;


fooid
foosubid
fooname


1
1
foo

1
1
foo

1
2
foo2

2
3
bar

null
4
null1

5
null
null2

null
null
null3



SELECT 7


SELECT * FROM foo
WHERE  fooid IS NOT NULL
AND    foosubid IS NOT NULL;


fooid
foosubid
fooname


1
1
foo

1
1
foo

1
2
foo2

2
3
bar



SELECT 4

fooid	foosubid	fooname
1	1	foo
1	1	foo
1	2	foo2
2	3	bar
null	4	null1
5	null	null2
null	null	null3