pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steve Atkins <steve@blighty.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Slow join using network address function
Date: Tue, 24 Feb 2004 09:10:26 -0800
Message-ID: <20040224171026.GD5368@gp.word-to-the-wise.com> (raw)
In-Reply-To: <22239.1077636202@sss.pgh.pa.us>
References: <000701c3fa02$e4e3d180$c300000a@caliente>
	<20040223160734.GA11052@gp.word-to-the-wise.com>
	<001d01c3face$bca62d40$c300000a@caliente>
	<22239.1077636202@sss.pgh.pa.us>

On Tue, Feb 24, 2004 at 10:23:22AM -0500, Tom Lane wrote:
> "Eric Jain" <Eric.Jain@isb-sib.ch> writes:
> >> <http://word-to-the-wise.com/ipr.tgz; is a datatype that contains 
> >> a range of IPv4 addresses, and which has the various operators to 
> >> make it GIST indexable.
> 
> > Great, this looks very promising.
> 
> >> No cast operators between ipr and inet types.
> 
> > Any way to work around this, short of dumping and reloading tables?
> 
> Wouldn't it be better to implement the GIST indexing operators of that
> package on the standard datatypes?  It wasn't apparent to me what "range
> of IP addresses" does for you that isn't covered by "CIDR subnet" for
> real-world cases.

Well, maybe.

However, many of the cases where people want to use this sort of
functionality (address range ownership, email blacklists etc) an
entity is likely to associated with one or a small number of ranges
of contiguous addresses. Those ranges are often not simple CIDR
blocks, and deaggregating them into a sequence of CIDR blocks
doesn't buy anything and complicates the problem.

I also managed to convince myself that it wasn't possible to do
a useful GIST index of a CIDR datatype - as the union between two
adjacent CIDR blocks as a CIDR block is often far, far larger than
the actual range involved - consider 63.255.255.255/32 and 64.0.0.0/32.
That seemed to break the indexing algorithms. I'd like to be proven
wrong on that, but would still find ipr a more useful datatype than
inet for my applications.

Cheers,
  Steve




view thread (10+ messages)  latest in thread

Message-ID: <20040224171026.GD5368@gp.word-to-the-wise.com>
Permalink:  ../20040224171026.GD5368@gp.word-to-the-wise.com/
Also on:    postgresql.org/message-id/20040224171026.GD5368@gp.word-to-the-wise.com

 · 

reply

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: steve@blighty.com
  Subject: Re: Slow join using network address function
  In-Reply-To: <20040224171026.GD5368@gp.word-to-the-wise.com>

* 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