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 base_table (
"year" INTEGER,
category TEXT,
country TEXT,
"class" INTEGER
);
INSERT INTO base_table ("year", category, country, "class")
VALUES
(2022, 'English', NULL, 1),
(2023, 'English', NULL, 2),
(2022, 'English', 'US', 1),
(2023, 'Science', NULL, 2);
CREATE TABLE inter_table (
"year" INTEGER,
country TEXT,
amount INTEGER
);
INSERT INTO inter_table ("year", country, amount)
VALUES
(2022, 'US', 100),
(2023, 'US', 400),
(2022, 'Europe', 300),
(2023, 'Europe', 200);
CREATE TABLE
INSERT 0 4
CREATE TABLE
INSERT 0 4
-- ORIGINAL SQL WITH MULTIPLE SUBQUERIES
select
a.year, a.category, a.country, a.class,
case
when a.country is null and category='English'
then
case
when class = 1 then (
select amount*5 from inter_table b
where country='US' and b.year=a.year
)
when class = 2 then
case
when (
select amount
from inter_table b
where a.country=b.country and b.year=a.year
) is not null
then (
select sum(amount)*5
from inter_table b
where country='US' and b.year=a.year
)
else null
end
end
else amount
end new_col
from base_table a
left join inter_table b
on a.year=b.year and a.country=b.country
order by a.year, a.category, a.country, a.class;
| year | category | country | class | new_col |
|---|---|---|---|---|
| 2022 | English | US | 1 | 100 |
| 2022 | English | null | 1 | 500 |
| 2023 | English | null | 2 | null |
| 2023 | Science | null | 2 | null |
SELECT 4
-- ADJUSTED SQL WITH ONE CTE
with us_agg as (
select country, year, sum(amount) as amount
from inter_table b
where country='US'
group by country, year
)
select
t.year, t.category, t.country, t.class,
case
when t.country is null and t.category='English'
then case
when t.class = 1 then us_agg.amount*5
when t.class = 2 then
case
when i.amount is not null then us_agg.amount*5
end
end
else i.amount
end new_col
from base_table t
left join inter_table i
on t.year = i.year and t.country = i.country
left join us_agg
on t.year = us_agg.year
order by t.year, t.category, t.country, t.class;
| year | category | country | class | new_col |
|---|---|---|---|---|
| 2022 | English | US | 1 | 100 |
| 2022 | English | null | 1 | 500 |
| 2023 | English | null | 2 | null |
| 2023 | Science | null | 2 | null |
SELECT 4
-- ADJUSTED SQL WITH ONE CTE AND FLATTENED CASE STATEMENTS
with us_agg as (
select country, year, sum(amount) as amount
from inter_table b
where country='US'
group by country, year
)
select
t.year, t.category, t.country, t.class,
case
when t.country is null and t.category='English' and t.class = 1
then us_agg.amount*5
when t.country is null and t.category='English' and t.class = 2 and i.amount is not null
then us_agg.amount*5
else i.amount
end new_col
from base_table t
left join inter_table i
on t.year = i.year and t.country = i.country
left join us_agg
on t.year = us_agg.year
order by t.year, t.category, t.country, t.class;
| year | category | country | class | new_col |
|---|---|---|---|---|
| 2022 | English | US | 1 | 100 |
| 2022 | English | null | 1 | 500 |
| 2023 | English | null | 2 | null |
| 2023 | Science | null | 2 | null |
SELECT 4