Postgres 13

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 items (id, name) as
  select 1, 'Fred'   union all
  select 2, 'Barney' 
2 rows affected

create table bids (bid_id, item_id) as
select 1, 1 union all
select 2, 1 union all
select 3, 1 union all
select 4, 1 union all
select 5, 1 union all
select 1, 2 union all
select 1, 2 
7 rows affected

select name, (select count(item_id) from bids where item_id = items.id)
from items
group by name;
ERROR:  subquery uses ungrouped column "items.id" from outer query
LINE 1: ..., (select count(item_id) from bids where item_id = items.id)
                                                              ^


select i.name, count(b.item_id)
  from items i
  inner join bids b
    ON b.item_id = i.id
  group by i.name


name
count


Fred
5

Barney
2