Re: Best way to index IP data?

Поиск
Список
Период
Сортировка
От Michael Stone
Тема Re: Best way to index IP data?
Дата
Msg-id 20080111220235.GX5294@mathom.us
обсуждение исходный текст
Ответ на Re: Best way to index IP data?  (Alvaro Herrera <alvherre@commandprompt.com>)
Ответы Re: Best way to index IP data?  (Andrew Sullivan <ajs@crankycanuck.ca>)
Список pgsql-performance
On Fri, Jan 11, 2008 at 06:37:10PM -0300, Alvaro Herrera wrote:
>So what this means is that our type oughta be optimized.  How about
>having a separate bit to indicate whether there is a netmask or not, and
>chop the storage earlier.  (I dunno if this already done)

Why not just have a type that indicates whether there is a netmask or
not? We currently have this (8.3 docs, which I see reflects the 3 byte
overhead--down to 20% rather than 50% for IPv6):

cidr    7 or 19 bytes    IPv4 and IPv6 networks
inet    7 or 19 bytes    IPv4 and IPv6 hosts and networks

Note that there's a type for (networks), and there's a type for (hosts and
networks), but there's a conspicuous lack of a type for (hosts). I
suppose if you really are sure that you want to store hosts and not
networks you should use inet and then set a constraint like
  if (family() == 4 && masklen() == 32)
  elsif (family() == 6 && masklen() == 128)

(For people whose databases don't resolve around network data, this
probably seems like not a big deal. OTOH, I can only imagine the outcry
if the only available arithmetic type was an intfloat, which can be
either an integer or a real number, has very low overhead to keep track
of whether there's a decimal point, and can easily be made to behave
like an integer if you set a constraint forbidding fractional parts.
Because, hey, you *never know* when you might need a real number, and
wouldn't want to paint yourself into a corner by stupidly specifying an
integer-only type.)

Mike Stone

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

Предыдущее
От: Michael Stone
Дата:
Сообщение: Re: Best way to index IP data?
Следующее
От: Andrew Sullivan
Дата:
Сообщение: Re: Best way to index IP data?