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?.
select version();
| version |
|---|
| PostgreSQL 11.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit |
create table invoice(invoicenum int);
create table invoiceitem(invoicenum int, layer int,invoiceitemnum int)
insert into invoice values(1),(2),(3),(4)
4 rows affected
insert into invoiceitem values(1,10,1), (1,0,2), (1,7,3), (1,0,4),(2,0,1),(2,3,2),(3,0,1)
7 rows affected
with cte as (
select
inv.invoicenum,sum(layer::int) "sum_layer"
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
group by 1
)
,
cte1 as (
select distinct on (inv.invoicenum) inv.invoicenum,layer, InvoiceItemNum
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
where inv.invoicenum in (select invoicenum from cte where sum_layer=0)
order by inv.invoicenum, InvoiceItemNum
),
cte2 as (
select
distinct on (inv.invoicenum) inv.invoicenum, layer , InvoiceItemNum
from invoice inv inner join invoiceitem item on item.invoicenum=inv.invoicenum
where inv.invoicenum in (select invoicenum from cte where sum_layer>0) and layer::int>0
order by inv.invoicenum, InvoiceItemNum
)
(
select * from cte1
union all
select * from cte2
)
order by 1
| invoicenum | layer | invoiceitemnum |
|---|---|---|
| 1 | 10 | 1 |
| 2 | 3 | 2 |
| 3 | 0 | 1 |