Postgres 12

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 tst(
  id serial,
  goods_id bigint,
  quant integer,
  value numeric);
  
create index idx1 on tst using btree(goods_id, quant);
create index idx2 on tst using btree(quant, goods_id);

insert into tst(goods_id, quant, value)
  select abs(g.id), q.q , v.v::numeric
    from generate_series(1, 100000, 1) as g(id)
   cross join lateral (select random() v
                        where g.id is not null) v
   cross join lateral (select (array[1,4,8])[abs(hashint4(g.id)) % 3 + 1] as q ) q;

select count(distinct goods_id), count(distinct quant) --10000000, 3
  from tst;

100000 rows affected



count
count


100000
3


create index idx3 on tst using btree(goods_id, quant);
create index idx4 on tst using btree(quant, goods_id);

select pg_relation_size('idx1'::regclass), 'idx1'
union all
select pg_relation_size('idx2'::regclass), 'idx2'
union all
select pg_relation_size('idx3'::regclass), 'idx3'
union all
select pg_relation_size('idx4'::regclass), 'idx4';


pg_relation_size
?column?


3162112
idx1

3186688
idx2

3178496
idx3

3178496
idx4


update tst set value = value + 0.5 where random() > 0.8;
19915 rows affected

select pg_relation_size('idx1'::regclass), 'idx1'
union all
select pg_relation_size('idx2'::regclass), 'idx2'
union all
select pg_relation_size('idx3'::regclass), 'idx3'
union all
select pg_relation_size('idx4'::regclass), 'idx4';


pg_relation_size
?column?


6324224
idx1

6316032
idx2

6324224
idx3

6332416
idx4