Postgres Pro
Downloads
Home   >   mailing lists

Thread: Help understanding indexes

Help understanding indexes

From
chris.gamble@CPBINC.com
Date:
I am working on a project that will use a large database (11 million
records). I have the following fields:

customerid     bigint
longitude    float8
latitude        float8

my indexes are:

idxcust        on customerid
idxloc        on longitude, latitude

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.

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???

Re: Help understanding indexes

From
Martijn van Oosterhout
Date:
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/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Re: Help understanding indexes

From
chris.gamble@CPBINC.com
Date:
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@svana.org]
> Sent:    Wednesday, June 12, 2002 10:38 AM
> To:    chris.gamble@CPBINC.com
> Cc:    pgsql-general@postgresql.org
> Subject:    Re: [GENERAL] Help understanding indexes
>
> 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/
> > There are 10 kinds of people in the world, those that can do binary
> > arithmetic and those that can't.

Re: Help understanding indexes

From
Tom Lane
Date:
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