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 table1 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    type INTEGER,
    col1 INTEGER,
    col2 INTEGER
);

-- Sample data for table1
INSERT INTO table1 (name, type, col1, col2)
VALUES
    ('Row 1', 5, 10, 20),
    ('Row 2', 7, 15, 25),
    ('Row 3', 10, 30, 40); 

CREATE TABLE debug (
  col1_arg INT,
  col2_arg INT
);

CREATE OR REPLACE FUNCTION function_name(col1_arg INTEGER, col2_arg INTEGER)
RETURNS TABLE (col1 INTEGER, col2 INTEGER)
AS $$
BEGIN
--    RAISE LOG 'Function called';
    INSERT INTO debug VALUES (col1_arg, col2_arg);
    RETURN QUERY SELECT col1_arg * 2 AS col1, col2_arg * 3 AS col2;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE

INSERT 0 3

CREATE TABLE

CREATE FUNCTION

TRUNCATE debug;

-- Test Query
SELECT t1.name, t1.type, t2.col1, t2.col2
FROM table1 t1
LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.type = 5;

SELECT * FROM debug;

TRUNCATE TABLE

name
type
col1
col2

Row 1
5
20
60

Row 2
7
null
null

Row 3
10
null
null

SELECT 3

col1_arg
col2_arg

10
20

15
25

30
40

SELECT 3

TRUNCATE debug;

SELECT t0.name, t0.type, t2.col1, t2.col2
FROM table1 t0
LEFT JOIN table1 t1 ON t0.id = t1.id AND t1.type = 5
LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.id IS NOT NULL;

SELECT * FROM debug;

TRUNCATE TABLE

name
type
col1
col2

Row 1
5
20
60

Row 2
7
null
null

Row 3
10
null
null

SELECT 3

col1_arg
col2_arg

10
20

SELECT 1