Re: query not using index - Mailing list pgsql-general

From Greg Janée
Subject Re: query not using index
Date
Msg-id 82B0CEED-34B4-430A-ADFE-445EE7A4659D@alexandria.ucsb.edu
Whole thread Raw
In response to Re: query not using index  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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@alexandria.ucsb.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


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: linux bug and lost rows
Next
From: Tom Lane
Date:
Subject: Re: Any "guide to indexes" exists?