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 ips (
id serial primary key,
ip INET
);
INSERT INTO ips
(ip)
VALUES
('10.0.0.4'),
('10.0.0.0/24'),
('10.1.0.0/23'),
('10.1.0.0/27');
create index on ips using gist(ip inet_ops,id);
select
a.id as id1,
a.ip as ip1,
b.id as id2,
b.ip as ip2
from ips a
inner join ips b
on a.ip <<= b.ip
and a.id<>b.id;
CREATE TABLE
INSERT 0 4
CREATE INDEX
| id1 | ip1 | id2 | ip2 |
|---|---|---|---|
| 1 | 10.0.0.4 | 2 | 10.0.0.0/24 |
| 4 | 10.1.0.0/27 | 3 | 10.1.0.0/23 |
SELECT 2