From: Michael Stone <mstone+postgres@mathom.us>
To: pgsql-performance@postgresql.org
Subject: Re: Best way to index IP data?
Date: Fri, 11 Jan 2008 17:02:36 -0500
Message-ID: <20080111220235.GX5294@mathom.us> (raw)
In-Reply-To: <20080111213710.GD8260@alvh.no-ip.org>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mstone+postgres@mathom.us
Subject: Re: Best way to index IP data?
In-Reply-To: <20080111220235.GX5294@mathom.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox