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

select setseed(0.1);
CREATE TABLE filez (
    id     INTEGER NOT NULL,
    name   TEXT NOT NULL,
    size   BIGINT NOT NULL
);
CREATE UNIQUE INDEX filez__id__idx ON filez (id);

setseed

SELECT 1

CREATE TABLE

CREATE INDEX

insert into filez select
generate_series(1,2e5),
gen_random_uuid(),
random()*1e3;

INSERT 0 200000

create function filez_up_to_limit(p_limit numeric, p_cutoff_id int default 0) 
  returns table(id int,name text,size bigint)
language plpgsql as $f$
declare rc cursor for select * from filez 
                      where filez.id>p_cutoff_id 
                      order by filez.id;
        current_size int:=0;
begin
 open rc;
 while current_size<p_limit loop
   fetch rc into id,name,size;
   return next;
   current_size:=current_size+size;
 end loop;
end $f$;

CREATE FUNCTION

explain analyze verbose 
select * from filez_up_to_limit(1e6);

QUERY PLAN

Function Scan on public.filez_up_to_limit  (cost=0.25..10.25 rows=1000 width=44) (actual time=6.680..6.791 rows=1978 loops=1)

  Output: id, name, size

  Function Call: filez_up_to_limit('1000000'::numeric, 0)

Planning Time: 0.037 ms

Execution Time: 6.961 ms

EXPLAIN

explain analyze verbose 
select * from filez_up_to_limit(1e7);

QUERY PLAN

Function Scan on public.filez_up_to_limit  (cost=0.25..10.25 rows=1000 width=44) (actual time=38.977..40.305 rows=20111 loops=1)

  Output: id, name, size

  Function Call: filez_up_to_limit('10000000'::numeric, 0)

Planning Time: 0.020 ms

Execution Time: 41.382 ms

EXPLAIN