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 shilpy (
region int,
s_code int,
b_code int,
d_code int
);
insert into shilpy values
(1, 10, 20, 30),
(1, 10, 20, 30),
(1, 10, 20, 30),
(1, 10, 20, 30),
(2, 100, 20, 30);
CREATE TABLE
INSERT 0 5
select *, (count_s + count_b + count_d) as total
from (
select region, count(s_code) as count_s, count(b_code) as count_b, count(d_code) as count_d
from shilpy
group by region
order by region
) as s
| region | count_s | count_b | count_d | total |
|---|---|---|---|---|
| 1 | 4 | 4 | 4 | 12 |
| 2 | 1 | 1 | 1 | 3 |
SELECT 2