Date: Fri, 11 Jan 2008 17:02:36 -0500
From: Michael Stone <mstone+postgres@mathom.us>
Subject: Re: Best way to index IP data?
In-reply-to: <20080111213710.GD8260@alvh.no-ip.org>
To: pgsql-performance@postgresql.org
Mail-followup-to: pgsql-performance@postgresql.org
Message-id: <20080111220235.GX5294@mathom.us>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii; format=flowed
Content-disposition: inline
References: <200801101614.54729.kevin@kevinkempterllc.com>
	<DA6DE53A-2F69-46F3-8C96-B341ADCDDFAF@blighty.com>
	<36e682920801101541o7626bbddj428617144240c554@mail.gmail.com>
	<Pine.LNX.4.64.0801111025390.30762@localhost>
	<13179.1200064791@sss.pgh.pa.us>
	<20080111162747.GT5294@mathom.us> <24044.1200082058@sss.pgh.pa.us>
	<20080111212703.GU5294@mathom.us>
	<20080111213710.GD8260@alvh.no-ip.org>
User-Agent: Mutt/1.5.13 (2006-08-11)

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