Message-ID: <45BE245D.3040006@streppone.it>
Date: Mon, 29 Jan 2007 17:44:13 +0100
From: Cosimo Streppone <cosimo@streppone.it>
User-Agent: Mozilla Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: Postgresql Performance list <pgsql-performance@postgresql.org>
Subject: Re: int4 vs varchar to store ip addr
References: <Pine.LNX.4.64.0701291644110.15162@localhost>
In-Reply-To: <Pine.LNX.4.64.0701291644110.15162@localhost>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Nicolas wrote:

> I have an authorization table that associates 1 customer IP to a service 
> IP to determine a TTL (used by a radius server).
> 
> table auth
>  client varchar(15);
>  service varchar(15);
>  ttl int4;
> client and service are both ip addr.
> 
> The number of distinct clients can be rather large (say around 4 
> million) and the number of distinct service around 1000.
> 
> there's a double index on ( client , service ).

It comes to mind another solution... I don't know if it is better or worse,
but you could give it a try.
Store IP addresses as 4 distinct columns, like the following:

     CREATE TABLE auth (
         client_ip1 shortint,
         client_ip2 shortint,
         client_ip3 shortint,
         client_ip4 shortint,
         service    varchar(15),
         ttl        int4,
     );

And then index by client_ip4/3/2/1, then service.

     CREATE INDEX auth_i1 ON auth (client_ip4, client_ip3, client_ip2, client_ip1);

or:

     CREATE INDEX auth_i1 ON auth (client_ip4, client_ip3, client_ip2, client_ip1, service);

I'm curious to know from pg internals experts if this could be a
valid idea or is totally non-sense.

Probably the builtin ip4 type is better suited for these tasks?

-- 
Cosimo