Re: match an IP address

Phoenix Kiula wrote:

> 1. What extra tax will this constraint levy on an INSERT or UPDATE on
> this table? There are about 100,000 inserts a day, and over three
> times as many UPDATES. The concurrency is pretty high -- I mean
> sometimes 1,000 users at the same time but no more than that. If the
> additional cost of insertion/updating is not too heavy, I suppose this
> could be a nice approach.

The best answer there is to do some testing. I wouldn't expect much of a
cost, but would recommend testing it to be sure.

> 2. Why not have an INET field...why a CIDR? What's the benefit? It
> stores those pesky ".../8" type additional data which one has to mask
> with functions. Would INET work just as well?

Yes, it would. I was just getting my types muddled.

> 3. Storage wise does this add significantly? How much space does an
> INET field take as opposed to, say, a VARCHAR field?

AFAIK nulls are not stored, they're just flagged in the null bitmap. As
such, there should be no or almost no storage cost.

> 4. Most importantly, how would you structure the index for this? I
> would much rather have a fast "=" in my sql's WHERE clause. No "OR"
> etc. Any thoughts?

I'd try a functional index first. If that didn't do the job, I'd use a
trigger-maintained column _purely_ as an optimisation (ie I could drop
it and lose no data) that stored text representations of the data.
Honestly, though, I expect the functional index would be more than good
enough and probably wouldn't have much of an INSERT/UPDATE cost.

Again, of course, I'd test before setting anything in stone.

--
Craig Ringer

--
Craig Ringer