[postgis-users] Newcomer-performance issue

[Jeff Hoffmann]

Pedro Doria Meunier wrote:

> select name, city from rede_estradas where
> 
> transform(geometry,32628) && 
> setsrid(expand(transform(geomfromtext('POINT(-16.851888 
> 32.646385)',4326),32628), 5), 32628)
> 
> and distance(transform(geomfromtext('POINT(-16.851888 
> 32.646385)',4326),32628), transform(geometry,32628)) <=5 limit 1;
> 
>  
> 
> Well… it ‘werks’… **but**! It takes ~1600ms to retrieve the result… L 
> imagine this for a fleet of, say, 500 vehicles…
> 
> I’m using Gist on the geometry column and VACCUM ANALYZEd the table.

This is just a guess, but your query is probably not using the index you 
created.  You should check this using explain, but if you created the 
index on the original column and not the transformed version, it won't 
be able to use the index.  If it's not using the index, try creating an 
index like this:

create index rede_estradas_index_geometry on rede_estradas using 
gist(transform(geometry, 32628) gist_geometry_ops);


-- 
Jeff Hoffmann
Head Plate Spinner
PropertyKey.com