Re: POSTGRES DB 3 800 000 rows table, speed up?

On Tue, Dec 27, 2005 at 11:25:37PM +0200, Eugene wrote:
> I've indexed first two columns they are IPfrom, IPto also table is btree
> version of postgre is 7.4.8, on hosting

You should ask them to upgrade; 7.4 is getting pretty old.

> I ask db like this SELECT * FROM ipdb2 WHERE '3229285376' BETWEEN ipfrom
> AND ipto;

I'm pretty sure PostgreSQL won't be able to use any indexes for this
(EXPLAIN ANALYZE would verify that). Instead, expand the between out:

WHERE ipfrom >= '...' AND ipto <= '...'

Also, generally speaking, databases and CamelCase don't mix too well;
you'll probably be happier doing something like ip_from and ip_to.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461