X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A1BD4D1BAD5 for ; Tue, 24 Feb 2004 17:17:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 14975-10 for ; Tue, 24 Feb 2004 13:17:13 -0400 (AST) Received: from gp.word-to-the-wise.com (gp.word-to-the-wise.com [64.71.176.18]) by svr1.postgresql.org (Postfix) with ESMTP id AA440D1BA35 for ; Tue, 24 Feb 2004 13:17:12 -0400 (AST) Received: by gp.word-to-the-wise.com (Postfix, from userid 500) id BB52C9029F7; Tue, 24 Feb 2004 09:10:26 -0800 (PST) Date: Tue, 24 Feb 2004 09:10:26 -0800 From: Steve Atkins To: pgsql-performance Subject: Re: Slow join using network address function Message-ID: <20040224171026.GD5368@gp.word-to-the-wise.com> References: <000701c3fa02$e4e3d180$c300000a@caliente> <20040223160734.GA11052@gp.word-to-the-wise.com> <001d01c3face$bca62d40$c300000a@caliente> <22239.1077636202@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <22239.1077636202@sss.pgh.pa.us> User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/282 X-Sequence-Number: 5806 On Tue, Feb 24, 2004 at 10:23:22AM -0500, Tom Lane wrote: > "Eric Jain" writes: > >> 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