Postgres 10

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(id serial primary key, userorder varbit unique);

CREATE TABLE

create function adj(orig varbit, b varbit) returns varbit language sql as $$
  select substring(orig for length(orig)-1)
       ||b
       ||substring(orig from length(orig));
$$;

CREATE FUNCTION

create function f(l varbit, h varbit) returns varbit language plpgsql as $$
  
begin
  if l is null and h is null then return B'1'; end if;
  if l is null then return adj(h,B'0'); end if;
  if h is null then return adj(l,B'1'); end if;
  if length(l)>length(h) then return adj(l,B'1'); end if;
  return adj(h,B'0');
end;
$$;

CREATE FUNCTION

insert into foo(userorder) values(f(null,null));
select * from foo order by userorder;

INSERT 0 1

id
userorder

1
1

SELECT 1

insert into foo(userorder) values(f(null,B'1'));
select * from foo order by userorder;

INSERT 0 1

id
userorder

2
01

1
1

SELECT 2

insert into foo(userorder) values(f(B'01',B'1'));
select * from foo order by userorder;

INSERT 0 1

id
userorder

2
01

3
011

1
1

SELECT 3

insert into foo(userorder)
values(f(B'01',B'011')),(f(B'011',B'1')),(f(B'1',null));
select * from foo order by userorder;

INSERT 0 3

id
userorder

2
01

4
0101

3
011

5
0111

1
1

6
11

SELECT 6