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

drop table if exists my_table;
create table my_table(id serial primary key, other_col int);
insert into my_table (other_col) values
(1), (2), (3), (4), (5), (8), (6), (7), (5), (8), (6), (7), (7);

table my_table;

DROP TABLE

CREATE TABLE

INSERT 0 13

id
other_col

1
1

2
2

3
3

4
4

5
5

6
8

7
6

8
7

9
5

10
8

11
6

12
7

13
7

SELECT 13

select array_agg(id) as ids
from my_table
group by other_col
having count(*) > 1;

ids

{5,9}

{7,11}

{8,12,13}

{6,10}

SELECT 4

select array_agg(id) as ids
from my_table
where id not in (
	select max(id)
	from my_table
	group by other_col
);

ids

{5,6,7,8,12}

SELECT 1