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 t231229 (
menu smallint
, option_type char(1)
, option_val char(1)
);
insert into t231229 values
(1, 'A', '1'),(1, 'A', '2'),
(2, 'A', '1'),(2, 'A', '2'),
(2, 'B', '1'),(2, 'B', '2'),
(3, 'A', '1'),(3, 'A', '2'),
(3, 'B', '1'),(3, 'B', '2'),
(3, 'C', '1'),(3, 'C', '2');
CREATE TABLE
INSERT 0 12
with t1 as (
select menu
, option_type || option_val as txt
from t231229
where menu = 1
), t2a as (
select menu
, option_type || option_val as txt
from t231229
where menu = 2
and option_type = 'A'
), t2b as (
select option_type || option_val as txt
from t231229
where menu = 2
and option_type = 'B'
), t3a as (
select menu
, option_type || option_val as txt
from t231229
where menu = 3
and option_type = 'A'
), t3b as (
select menu
, option_type || option_val as txt
from t231229
where menu = 3
and option_type = 'B'
), t3c as (
select menu
, option_type || option_val as txt
from t231229
where menu = 3
and option_type = 'C'
)
select *
| menu | txt |
|---|---|
| 1 | A1 |
| 1 | A2 |
| 2 | A1B1 |
| 2 | A1B2 |
| 2 | A2B1 |
| 2 | A2B2 |
| 3 | A1B1:C1 |
| 3 | A1B1:C2 |
| 3 | A1B2:C1 |
| 3 | A1B2:C2 |
| 3 | A2B1:C1 |
| 3 | A2B1:C2 |
| 3 | A2B2:C1 |
| 3 | A2B2:C2 |
SELECT 14