From: Cosimo Streppone <cosimo@streppone.it>
To: Postgresql Performance list <pgsql-performance@postgresql.org>
Subject: Re: int4 vs varchar to store ip addr
Date: Mon, 29 Jan 2007 17:44:13 +0100
Message-ID: <45BE245D.3040006@streppone.it> (raw)
In-Reply-To: <Pine.LNX.4.64.0701291644110.15162@localhost>
References: <Pine.LNX.4.64.0701291644110.15162@localhost>
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
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: cosimo@streppone.it
Subject: Re: int4 vs varchar to store ip addr
In-Reply-To: <45BE245D.3040006@streppone.it>
* 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