Help understanding indexes

Which FAQ is the bigint information in? I thought I looked at all of the
postgres FAQs before I asked.

Heres the one with the float8's with an explain.

SELECT * FROM tdatcustomerlist WHERE float8gt(longitude,-87.09486892480946)
AND float8lt(longitude, -87.05713307519055) AND float8gt(latitude,
31.095787219971054) AND float8lt(latitude, 31.124730780028944)

explain:
Seq Scan on tdatcustomerlist (cost=0.00..240915.40 rows=47875 width=251)

Thank you for the help.

On Wed, Jun 12, 2002 at 10:18:50AM -0500, chris.gamble@CPBINC.com wrote:

I am working on a project that will use a large database (11 million
records). I have the following fields:

I run queries like
SELECT * FROM tdatcustomerlist WHERE customerid=4237095

but postgres will not use my indexes. I have found the workaround here as:
SELECT * FROM tdatcustomerlist WHERE customerid=4237095::BIGINT

but this is not very portable sql.

what about:

SELECT * FROM tdatcustomerlist WHERE customerid='4237095'

This is a FAQ BTW. I don't think leaving the quotes off will work in the
long run anyway. Say you get customer number 10^12 (you must be expecting
large if you're using a bigint), then the parser will read your number,
convert it to an integer and fail. Quoting saves the conversion until it
know it wants a bigint.

also, i have a query that tries to do greater than less than queries against
long / lat fields, but I have not yet discovered a way to get those queries
to use indexes. I have tried using the float8gt/lt functions, and tried
using type casting. Does anyone have helpful explanations???

Should work, if the clauses are selective enough. Do you have an EXPLAIN of
an odd query?
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

chris.gamble@CPBINC.com writes:

Heres the one with the float8's with an explain.

SELECT * FROM tdatcustomerlist WHERE float8gt(longitude,-87.09486892480946)
AND float8lt(longitude, -87.05713307519055) AND float8gt(latitude,
31.095787219971054) AND float8lt(latitude, 31.124730780028944)

Try writing it in a more natural fashion:

SELECT * FROM tdatcustomerlist WHERE longitude > -87.09486892480946
AND longitude < -87.05713307519055 AND latitude > 31.095787219971054
AND latitude < 31.124730780028944

The optimizer doesn't try to do anything with function calls, only with
operators.

regards, tom lane