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