Re: query not using index

Thanks, that was the problem: postgres thought it had to typecast the
column to a box type, which prevented use of the index.

For any PostGIS users reading this: the solution is to express the
other operand using a GeometryFromText(...) construct.

Thanks again,
-Greg

On May 5, 2007, at 7:48 AM, Tom Lane wrote:

> =?ISO-8859-1?Q?Greg_Jan=E9e?= <gjanee(at)alexandria(dot)ucsb(dot)edu> writes:
>> db=> explain analyze SELECT * FROM scene A WHERE A.footprint && box
>> '((-120.1, 34.3), (-119.7, 34.4))' ;
>> QUERY PLAN
>> ---------------------------------------------------------------------
>> ---
>> -------------------------------------------
>> Seq Scan on scene a (cost=0.00..369700.89 rows=42196 width=252)
>> (actual time=50.064..47748.609 rows=507 loops=1)
>> Filter: ((footprint)::box && '(-119.7,34.4),(-120.1,34.3)'::box)
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>> Total runtime: 47749.094 ms
>> (3 rows)
>
> This appears to be using the "box && box" operator. I'm not sure
> which
> operators a GIST geometry index supports, but evidently that's not one
> of them. You probably want to cast the other operand differently.
> How, I dunno --- the postgis lists would be a better place to ask
> than here.
>
> regards, tom lane