Postgres 11

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 mytable(
   id2 VARCHAR(11) 
  ,num INTEGER 
);
INSERT INTO mytable(id2,num) VALUES ('28',6);
INSERT INTO mytable(id2,num) VALUES ('28',104);
INSERT INTO mytable(id2,num) VALUES ('28',106);
INSERT INTO mytable(id2,num) VALUES ('50',6);
INSERT INTO mytable(id2,num) VALUES ('50',104);

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select id2, num
from (
    select
           id2, num
         , count(*) over(partition by num) c_num
         , ca.c_id2
    from mytable
    left join lateral (select count(distinct id2) c_id2 from mytable) ca on true
    ) d
where c_num = c_id2
;




id2
num


28
6

50
6

28
104

50
104


drop table mytable;

CREATE TABLE mytable(
   id2 VARCHAR(11) 
  ,num INTEGER 
);
INSERT INTO mytable(id2,num) VALUES ('29',1);
INSERT INTO mytable(id2,num) VALUES ('30',1);
INSERT INTO mytable(id2,num) VALUES ('31',1);


1 rows affected

1 rows affected

1 rows affected

select id2, num
from (
    select
           id2, num
         , count(*) over(partition by num) c_num
         , ca.c_id2
    from mytable
    left join lateral (select count(distinct id2) c_id2 from mytable) ca on true
    ) d
where c_num = c_id2
;


id2
num


29
1

30
1

31
1