index for inet and >> (contains) function

Поиск
Список
Период
Сортировка
От Richard Jones
Тема index for inet and >> (contains) function
Дата
Msg-id 20060322163535.GA17175@furbychan.cocan.org
обсуждение исходный текст
Ответы Re: index for inet and >> (contains) function
Re: index for inet and >> (contains) function
Список pgsql-general
I've got a table like this:

  create table iptocountry (
    network inet not null,
    countryid int not null references countries (id)
  );

The idea is that it contains mappings from IP address ranges to
countries, something like this:

  insert into iptocountry values ('1.2.3.0/24', 33);

It contains a lot of rows (some 8 million, taken from hostip.info).
Unfortunately when I use the "contains" function (>>) I get a
sequential scan, as in:

  select countryid from iptocountry where network >> '1.2.3.4';

Is there a suitable index that I can put on the network field to fix
this?

Rich.

--
Richard Jones, CTO Merjis Ltd.
Merjis - web marketing and technology - http://merjis.com
Team Notepad - intranets and extranets for business - http://team-notepad.com

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

Предыдущее
От: "Andrus"
Дата:
Сообщение: Re: How to release locks
Следующее
От: Marek Lewczuk
Дата:
Сообщение: java_reset - how it works