[GENERAL] index for inet and >> (contains) function

[Richard Jones]

I've got a table like this:

  create table iptocountry (
network inet not null,
countryid int not null references countries (id)
  );

The idea is that it contains mappings from IP address ranges to
countries, something like this:

  insert into iptocountry values ('1.2.3.0/24', 33);

It contains a lot of rows (some 8 million, taken from hostip.info).
Unfortunately when I use the "contains" function (>>) I get a
sequential scan, as in:

  select countryid from iptocountry where network >> '1.2.3.4';

Is there a suitable index that I can put on the network field to fix
this?

Rich.

-- 
Richard Jones, CTO Merjis Ltd.
Merjis - web marketing and technology - http://merjis.com
Team Notepad - intranets and extranets for business - http://team-notepad.com

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] ANNOUNCE: Type-safe interface to PostgreSQL

[Richard Jones]

This may be of interest to some people on this list.  It's a complete
type-safe interface to PostgreSQL, from Objective CAML (OCaml) where
statements are checked not just for syntax, but for types, at compile
time.

  http://merjis.com/developers/pgocaml

It works by shuffling the statements off to the database at compile
time, PREPARE-ing them, and then DESCRIBE-ing them.

In addition, it's pure OCaml.  It doesn't use libpq at all (which has
advantages and disadvantages as it turns out).

Rich.

-- 
Richard Jones, CTO Merjis Ltd.
Merjis - web marketing and technology - http://merjis.com
Team Notepad - intranets and extranets for business - http://team-notepad.com

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] SQL ASCII encoding

[Richard Jones]

On Wed, Apr 05, 2006 at 10:15:18PM +0200, Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> Martijn van Oosterhout  writes:
> 
> > As a british user, latin9 will cover most of your needs, unless
> > ofcourse someone wants to enter their name in chinese :)
> 
> Since british users don't use French OE ligatures or Euro currency
> signs, even latin1 would do.

However as a British PostgreSQL user, I would really like to encourage
the O.P. to use UNICODE for _every_ database.

My question: Is it possible to upgrade a database from ASCII to
UNICODE without dumping and restoring?

Rich.

-- 
Richard Jones, CTO Merjis Ltd.
Merjis - web marketing and technology - http://merjis.com
Team Notepad - intranets and extranets for business - http://team-notepad.com

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster