Re: Help understanding indexes

On Wed, Jun 12, 2002 at 10:18:50AM -0500, chris(dot)gamble(at)CPBINC(dot)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(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.