Postgres 13

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 wyplaty(id INT);
INSERT INTO wyplaty VALUES (10),(20)

CREATE TABLE

INSERT 0 2

CREATE SEQUENCE wyplaty_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 7771
  CACHE 1;

CREATE SEQUENCE

SELECT *
FROM wyplaty

id

10

20

SELECT 2

CREATE OR REPLACE FUNCTION f_wyplaty()
RETURNS TABLE(id INTEGER)
AS $$
DECLARE 
     b    integer;
BEGIN
    b := nextval('wyplaty_seq');
    RETURN QUERY
    SELECT *
    FROM wyplaty;
END
$$ LANGUAGE plpgsql;

CREATE FUNCTION

SELECT last_value FROM wyplaty_seq;

last_value

7771

SELECT 1

SELECT *
FROM f_wyplaty()

id

10

20

SELECT 2

SELECT last_value FROM wyplaty_seq;

last_value

7771

SELECT 1

SELECT *
FROM f_wyplaty()

id

10

20

SELECT 2

SELECT last_value FROM wyplaty_seq;

last_value

7772

SELECT 1

SELECT *
FROM f_wyplaty()

id

10

20

SELECT 2

SELECT *
FROM f_wyplaty()

id

10

20

SELECT 2

SELECT last_value FROM wyplaty_seq;

last_value

7774

SELECT 1