Efficient CIDR Range Lookups Using Expression Indexes

				
					DROP TABLE IF EXISTS network_data;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

--
-- Helper function to normalize IPv4 and IPv6 addresses to a 16-byte (128-bit)
-- format bytea (binary format). This function is marked IMMUTABLE so that it
-- can be used in an expression based index or as a generate column expression.
-- expression.
--
-- Explanation:
--     * network(cidr) → returns the start address of the CIDR.
--     * host(...) → returns the textual representation (e.g., '192.168.1.0').
--     * For IPv4, we prefix with '::ffff:' to make it an IPv6-mapped address.
--     * inet_send() → converts the inet to its raw binary (bytea) representation.
--
CREATE OR REPLACE FUNCTION inet_to_byte16(i inet) RETURNS bytea
 LANGUAGE sql IMMUTABLE RETURNS NULL ON NULL INPUT
AS $$
  SELECT CASE
     WHEN family(i) = 4 THEN
         inet_send(inet('::ffff:' || host(i)))
     ELSE
         inet_send(i)
    END;
$$;

-- Get the start address of a CIDR range and convert it to a 16 byte binary value
CREATE OR REPLACE FUNCTION cidr_start_addr_16(c cidr) RETURNS bytea
 LANGUAGE sql IMMUTABLE RETURNS NULL ON NULL INPUT
AS $$
  SELECT inet_to_byte16(network(c));
$$;

-- Get the end address of a CIDR range and convert it to a 16 byte binary value
CREATE OR REPLACE FUNCTION cidr_end_addr_16(c cidr) RETURNS bytea
 LANGUAGE sql IMMUTABLE RETURNS NULL ON NULL INPUT
AS $$
  SELECT inet_to_byte16(broadcast(c));
$$;

CREATE TABLE network_data (
      id  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      cidr_col CIDR NOT NULL,
      cidr_start_addr bytea GENERATED ALWAYS AS (cidr_start_addr_16(cidr_col)) STORED,
      cidr_end_addr  bytea GENERATED ALWAYS AS (cidr_end_addr_16(cidr_col)) STORED,
      payload TEXT);

--
-- To implement efficient queries for looking up the the relevant row
-- for a target_ip (i.e. which row has the CIDR range for a IP we
-- want to lookup) we will use an index on the start of the CIDR range.
-- This works fine if the CIDR ranges in the table are non-overlapping.
-- We will include the cidr_end_addr also in the index so that both
-- conditions can be checked and filtered before going to main table
-- for the other columns of the matching row.
--
CREATE INDEX cidr_start_index ON network_data(cidr_start_addr ASC) INCLUDE (cidr_end_addr);

-- Let us load some sample data
INSERT INTO network_data(cidr_col, payload)
VALUES ('10.0.0.0/8', 'row-1'),
       ('172.16.0.0/12', 'row-2'),
       ('192.168.1.0/24', 'row-3'),
       ('198.51.100.0/29', 'row-4'),
       ('127.0.0.0/8', 'row-5'),
       ('2001:db8::/32', 'row-6'),
       ('fe80::/10', 'row-7'),
       ('2607:f8b0:4000::/48', 'row-8'),
       ('::1/128', 'row-9'),
       ('194.155.2.0/24', 'row-10');

-- Verify the test data
SELECT cidr_col,
       network(cidr_col) as ip_start,
       broadcast(cidr_col) as ip_end,
       cidr_start_addr,
       cidr_end_addr
FROM network_data LIMIT 10;

-- Sample query
SELECT *
FROM network_data
WHERE cidr_start_addr <= inet_to_byte16('194.155.2.7')
 AND  cidr_end_addr >= inet_to_byte16('194.155.2.7')
ORDER by cidr_start_addr DESC LIMIT 1;