[postgis-users] Problem with &&-operator and transform()

[strk at refractions.net]

On Mon, Jul 11, 2005 at 01:57:50PM +0200, Christian Schwartze wrote:
> Hi to all PostGIS-users....
> 
> I have a table "earthquakes" with a GiSt-index on column "geo". I search for all earthquakes along a street using a buffer distance.
> Does somebody know, why the GiST-index is not in use (as per explain), when combining the &&-operator with the transform()-function like in:
> 
> select quake_id, location, x(geo), y(geo) 
> from earthquakes 
> where geo && envelope(buffer(transform((select geo from roads where name = 'U.S. Hwy 85'),102009),5)) and within(geo,(select buffer(transform((select geo from roads where name = 'U.S. Hwy 85'),102009),5)));
> 
> Without the transform-function the query is very quick because of the GiST-index, but i need the transform-function to calculate in unit "meter".
> 
> Can somebody help me, thanks a lot!

You can use 'set enable_seqscan off' to force index usage.
Use 'explain analyze' to see how good the estimator job is.
Show us both explain analyze and postgis_full_version() outputs
to go deeper.

--strk;