Hi,
I have a table like this:
CREATE TABLE networks (
iprange CIDR,
datum INTEGER
);
and I want to efficiently support queries like this:
SELECT * FROM networks WHERE '128.3.4.5' <<= iprange;
There doesn't seem to be any indexing mechanism in core PostgresSQL that
supports this; it always does a sequential scan.
I've looked at two possibilities so far:
1) ip4r. This is a non-core module and also only handles IPv4. I'd prefer
to stick with the native PostgreSQL data types.
2) For our application, we can limit iprange to a /8 at biggest, so
another option is to expand the query like this:
SELECT * FROM networks WHERE iprange IN (
'128.3.4.5/32','128.3.4.4/31','128.3.4.4/30','128.3.4.0/29','128.3.4.0/28',
'128.3.4.0/27','128.3.4.0/26','128.3.4.0/25','128.3.4.0/24','128.3.4.0/23',
'128.3.4.0/22','128.3.0.0/21','128.3.0.0/20','128.3.0.0/19','128.3.0.0/18',
'128.3.0.0/17','128.3.0.0/16','128.2.0.0/15','128.0.0.0/14','128.0.0.0/13',
'128.0.0.0/12','128.0.0.0/11','128.0.0.0/10','128.0.0.0/9', '128.0.0.0/8');
which lets you use a btree index, but seems grotesque to me. This kind of
query seems like a common thing to want to do... anyone have any good ideas
how to do it efficiently?
Regards,
David.
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
