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

I have a question about this, shouldn't it be the query should look like

SELECT *
FROM ipdb2
WHERE 3229285376 BETWEEN ipfrom AND ipto

Note the query doesn't quote the filter, since the ipfrom and ipto are both
integer types?

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Jim C. Nasby
Sent: Wednesday, December 28, 2005 11:33 AM
To: Eugene
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings