Re: 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.

> -----Original Message-----
> From: Martijn van Oosterhout [SMTP:kleptog(at)svana(dot)org]
> Sent: Wednesday, June 12, 2002 10:38 AM
> To: chris(dot)gamble(at)CPBINC(dot)com
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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.