Postgres 16

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 ip_table (
    ip BIGINT,
    value INT
);
CREATE TABLE subnet_list (
    subnet BIGINT
);

CREATE TABLE



CREATE TABLE


INSERT INTO ip_table (ip, value) VALUES
    (2379284488, 2),
    (2379284482, 4),
    (3226028805, 3);
INSERT INTO subnet_list (subnet) VALUES
    (3226028800),
    (2379284480);


INSERT 0 3



INSERT 0 2


WITH subnet_list AS (
    SELECT unnest(ARRAY[3226028800, 2379284480]) AS subnet
)
SELECT 
    SL.subnet AS net,
    ip_table.ip,
    ip_table.value
FROM
    subnet_list SL
JOIN 
    ip_table ON (ip_table.ip & SL.subnet) = SL.subnet;


net
ip
value


2379284480
2379284488
2

2379284480
2379284482
4

3226028800
3226028805
3



SELECT 3

net	ip	value
2379284480	2379284488	2
2379284480	2379284482	4
3226028800	3226028805	3