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 A (
raw_number int,
ars int,
bill int,
codt varchar(20),
c4 int
);
insert into A values
(1, 900, 603121, '', 123),
(2, 900, 603121, 'YPR-003', 234),
(3, 900, 603121, 'Psp-123', 345),
(4, 900, 603121, '', 456),
(5, 900, 603121, '', 567);
create table B (
raw_number int,
ars int,
bill int,
codt varchar(20),
s4 varchar(20)
);
insert into B values
(1, 900, 603121, 'Psp-123', 'asd'),
(2, 900, 603121, '', 'zxc'),
(3, 900, 455000, '', 'F');
CREATE TABLE
INSERT 0 5
CREATE TABLE
INSERT 0 3
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a
LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt and a.codt <> ''
order by ars, bill, c4
| ars | bill | codt | c4 | s4 |
|---|---|---|---|---|
| 900 | 603121 | null | 123 | null |
| 900 | 603121 | null | 234 | null |
| 900 | 603121 | Psp-123 | 345 | asd |
| 900 | 603121 | null | 456 | null |
| 900 | 603121 | null | 567 | null |
SELECT 5
select s.ars, s.bill, s.codt, s.c4, case when s.s4 is not null then s.s4 else b.s4 end as s4
from B b
inner join (
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a
LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt and a.codt <> ''
order by ars, bill, c4
) as s on s.ars = b.ars and s.bill = b.bill
where b.codt = ''
| ars | bill | codt | c4 | s4 |
|---|---|---|---|---|
| 900 | 603121 | null | 123 | zxc |
| 900 | 603121 | null | 234 | zxc |
| 900 | 603121 | Psp-123 | 345 | asd |
| 900 | 603121 | null | 456 | zxc |
| 900 | 603121 | null | 567 | zxc |
SELECT 5