Skip site navigation (1) Skip section navigation (2)

Re: Slow join using network address function

From: Steve Atkins <steve(at)blighty(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Slow join using network address function
Date: 2004-02-24 17:10:26
Message-ID: 20040224171026.GD5368@gp.word-to-the-wise.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Feb 24, 2004 at 10:23:22AM -0500, Tom Lane wrote:
> "Eric Jain" <Eric(dot)Jain(at)isb-sib(dot)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


In response to

pgsql-performance by date

Next:From: Richard HuxtonDate: 2004-02-24 17:11:20
Subject: Re: [SQL] Materialized View Summary
Previous:From: Josh BerkusDate: 2004-02-24 16:59:32
Subject: Re: Column correlation drifts, index ignored again

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group