Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E23E59FB1D9 for ; Thu, 1 Feb 2007 15:30:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 79820-03 for ; Thu, 1 Feb 2007 15:30:45 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 594549F9F0A for ; Thu, 1 Feb 2007 15:30:44 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so496256ugf for ; Thu, 01 Feb 2007 11:30:43 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=XsxtwFNjtqOzMPzoHlRxNemWLesfeK6DEzjFb6Q8o3miNqWQdxLWrjiQxhZiSrcd2pkoUGBAnrqWdNUU694j7IsftHKvW23t5WFdC/EtwjecJM98PPqzWexsN2Ym9qWc14K/ggz7C3e2pIxbO8jHaCYARbnegv4pEf2doB7DHlE= Received: by 10.82.163.13 with SMTP id l13mr831929bue.1170358242659; Thu, 01 Feb 2007 11:30:42 -0800 (PST) Received: by 10.82.190.6 with HTTP; Thu, 1 Feb 2007 11:30:42 -0800 (PST) Message-ID: Date: Thu, 1 Feb 2007 14:30:42 -0500 From: "Michael Artz" To: "Pomarede Nicolas" Subject: Re: int4 vs varchar to store ip addr Cc: "Florian Weimer" , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <82ac014x4t.fsf@mid.bfk.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/10 X-Sequence-Number: 23064 On 1/30/07, Pomarede Nicolas wrote: > On Mon, 29 Jan 2007, Florian Weimer wrote: > > > * Pomarede Nicolas: > > > >> I could use PG internal inet/cidr type to store the ip addrs, which > >> would take 12 bytes per IP, thus gaining a few bytes per row. > > > > I thought it's down to 8 bytes in PostgreSQL 8.2, but I could be > > mistaken. > > > >> Apart from gaining some bytes, would the btree index scan be faster > >> with this data type compared to plain varchar ? > > > > It will be faster because less I/O is involved. > > > > For purposes like yours, there is a special ip4 type in a contributed > > package which brings down the byte count to 4. I'm not sure if it's > > been ported to PostgreSQL 8.2 yet. > > Yes thanks for this reference, ip4r package seems to be a nice addition to > postgres for what I'd like to do. Does someone here have some real life > experience with it (regarding performance and stability) ? I'm using IP4 and have not had a problem with it in 8.2 (or 8.1) in terms of stability. As I designed my DB using it, I don't really have any comparisons to inet and/or varchar. One of the most useful things for me is the ability to create a GIST index to support determination of range inclusion (i.e. 192.168.23.1 is in the 192.168/16 network range), although it doesn't sound like this would be useful to you. -Mike