Postgres 14

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


-- create aggregates that record the series of transform calls (these are
-- intentionally not true inverses)


create function logging_sfunc_strict(text, anyelement)
returns text as
$$
select $1 || '*' || quote_nullable($2)
$$
LANGUAGE sql strict IMMUTABLE;

create or replace function logging_msfunc_strict(text,anyelement)
returns text as
$$
select $1 || '+' || quote_nullable($2)
$$
LANGUAGE sql strict IMMUTABLE;

create or replace function logging_minvfunc_strict(text, anyelement)
returns text as
$$
select $1 || '-' || quote_nullable($2)
$$
LANGUAGE sql strict IMMUTABLE;

create aggregate logging_agg_strict(text)
(
    stype = text,
    sfunc = logging_sfunc_strict,
    mstype =  text,
    msfunc = logging_msfunc_strict,
    minvfunc = logging_minvfunc_strict
);


create aggregate logging_agg_strict_initcond(anyelement)
(
    stype = text,
    sfunc = logging_sfunc_strict,
    mstype = text, 
    msfunc = logging_msfunc_strict,
    minvfunc = logging_minvfunc_strict,
    initcond = 'I',
    minitcond = 'MI'
);
-- test strict and non-strict cases
select 
    p::text || ','||i::text||':'|| COALESCE(v::text,'NULL') as _row
    ,logging_agg_strict(v) over w as nstrict
    ,logging_agg_strict_initcond(v) over w as nstrict
from(values
    (1,1,NULL),
    (1,2,'a'),
    (1,3,'b'),
    (1,4,NULL),
    (1,5,NULL),
    (1,6,'c'),
    (2,1,NULL),
    (2,2,'x'),
    (3,1,'z')
) as t(p,i,v)
window w as (
    partition by p order by i rows between 1 preceding and current row
    );


_row
nstrict
nstrict


1,1:NULL
null
MI

1,2:a
a
MI+'a'

1,3:b
a+'b'
MI+'a'+'b'

1,4:NULL
a+'b'-'a'
MI+'a'+'b'-'a'

1,5:NULL
null
MI

1,6:c
c
MI+'c'

2,1:NULL
null
MI

2,2:x
x
MI+'x'

3,1:z
z
MI+'z'


select p,logging_agg_strict(v)
from(values
    (1,1,NULL),
    (1,2,'a'),
    (1,3,'b'),
    (1,4,NULL),
    (1,5,NULL),
    (1,6,'c'),
    (2,1,NULL),
    (2,2,'x'),
    (3,1,'z')
) as t(p,i,v)
group by 1;


p
logging_agg_strict


1
a*'b'*'c'

2
x

3
z


select logging_msfunc_strict('hello', NULL::text);
select logging_msfunc_strict( NULL::text,'hello'::text);
select logging_minvfunc_strict(NULL, 'hi'::text);
select logging_minvfunc_strict('hi'::text, NULL::text);


logging_msfunc_strict


null




logging_msfunc_strict


null




logging_minvfunc_strict


null




logging_minvfunc_strict


null

_row	nstrict	nstrict
1,1:NULL	null	MI
1,2:a	a	MI+'a'
1,3:b	a+'b'	MI+'a'+'b'
1,4:NULL	a+'b'-'a'	MI+'a'+'b'-'a'
1,5:NULL	null	MI
1,6:c	c	MI+'c'
2,1:NULL	null	MI
2,2:x	x	MI+'x'
3,1:z	z	MI+'z'