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 test_dp (
"firsttt" VARCHAR(10),
"secondd" INTEGER
);
INSERT INTO test_dp
("firsttt", "secondd")
VALUES
('banana', '1'),
('apple', '1'),
('banana', '45'),
('banana', '1'),
('kiwi', '2'),
('grape', '2'),
('grape', '33'),
('strawberry', '56'),
('strawberry', '56');
9 rows affected
SELECT
DISTINCT ON (secondd) test_dp.firsttt,
test_dp.secondd
FROM
test_dp
order by secondd;
| firsttt | secondd |
|---|---|
| banana | 1 |
| kiwi | 2 |
| grape | 33 |
| banana | 45 |
| strawberry | 56 |
select distinct(dp1.*) from test_dp dp1
inner join test_dp dp2
on dp1.firsttt = dp2.firsttt
and dp1.secondd <> dp2.secondd
| firsttt | secondd |
|---|---|
| banana | 45 |
| banana | 1 |
| grape | 2 |
| grape | 33 |