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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jim C. Nasby
Sent: Wednesday, December 28, 2005 11:33 AM
To: Eugene
Cc: pgsql-general@postgresql.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@pervasive.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
