> 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 (
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);
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?
In response to
pgsql-performance by date
|Next:||From: Carlos Moreno||Date: 2007-01-29 19:47:46|
|Subject: Re: [OT] Very strange postgresql behaviour|
|Previous:||From: Florian Weimer||Date: 2007-01-29 16:26:42|
|Subject: Re: int4 vs varchar to store ip addr|