Skip site navigation (1) Skip section navigation (2)

Re: match an IP address

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>
Cc: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Re: match an IP address
Date: 2008-09-23 10:49:11
Message-ID: 48D8C9A7.909@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-general
Phoenix Kiula wrote:

> Ever tried this crap on a table of 10 million records on a live
>  website, where this query is happening at 3000 times per second? No
>  such function schtick will match the raw speed of a simpler indexed
>  query. Or did you mean my index should contain the COALESCE already?

Hmm. My previous response may have been overly grumpy.

The point I was *trying* to make is that shoving a username/id and an IP
address into a single field is probably not ideal. At least in my
experience you pay for this sort of optimisation (if it even works out
as an optimisation in the first place) down the track. I have the
misfortunate to have to administrate a system full of such multi-use
fields, and have developed a real loathing for the approach.

If you don't want to store IPs for registered users, I'd use:

user_id INTEGER,
ip cidr,
CONSTRAINT must_have_userstamp
CHECK ( user_id IS NOT NULL OR ip IS NOT NULL)

... and yes, I'd use a functional index to look it up, or even a
trigger-maintained cache of the text representation if I had to. Then
again, I guess I'm lucky enough to work in environments where data
integrity and correctness is a priority and the resources available are
a good fit to the tasks the database needs to do.

--
Craig Ringe

In response to

Responses

pgsql-general by date

Next:From: Phoenix KiulaDate: 2008-09-23 11:16:50
Subject: Re: match an IP address
Previous:From: Phoenix KiulaDate: 2008-09-23 10:47:50
Subject: Re: match an IP address

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group