Efficiently searching for CIDRs containing an IP address

Поиск
Список
Период
Сортировка
От David F. Skoll
Тема Efficiently searching for CIDRs containing an IP address
Дата
Msg-id 4A1FF249.2020301@roaringpenguin.com
обсуждение исходный текст
Ответы Re: Efficiently searching for CIDRs containing an IP address
Re: Efficiently searching for CIDRs containing an IP address
Список pgsql-admin
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.


В списке pgsql-admin по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Continuous archiving fails routinely with "invalid magic number" error
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Efficiently searching for CIDRs containing an IP address