[postgis-users] Newcomer-performance issue

[Gregory S. Williamson]

Pedro  (got the name right this time, disculpeme!) posted an analyze on the JUMP users list, repdicued below. It looks to me as if the index is not being used and its doing a sequential scan ... Perhaps if you could post the table structure and indexes, that might help.


About the size of the LINESTRING layer: it's about 9000 segments.
The POINT errr... 'layer' ... is just the current vehicle's gps position.

Two geometry fields with different srids within the same layer?! Is it
possible?
Going to try it although I predict lots of problems with OpenJump... ;-)

Btw: I totally forgot about EXPLAIN ANALYZE... eheheh shame on me...
Here's the output: (I immediately pinpointed that 'Seq Scan' ... (!)
--------------------------------------------------------------------
 Limit  (cost=0.00..520.33 rows=1 width=21) (actual time=1537.542..1537.543
rows
=1 loops=1)
   ->  Seq Scan on rede_estradas  (cost=0.00..520.33 rows=1 width=21)
(actual ti
me=1537.539..1537.539 rows=1 loops=1)
         Filter: ((transform(geometry, 32628) &&
'0103000020747F0000010000000500
0000000000206FEA1341000000E0CD914B41000000206FEA134100000000D3914B4100000040
97EA
134100000000D3914B410000004097EA1341000000E0CD914B41000000206FEA1341000000E0
CD91
4B41'::geometry) AND
(distance('0101000020747F0000D1F1243D83EA13410C6C276DD0914B
41'::geometry, transform(geometry, 32628)) <= 5::double precision))
 Total runtime: 1537.572 ms
(4 rows)

(sorry for the cut and paste errors) ... so no index being used seems to be the issue. Post the DDL for the table and someone might be able to help more.

Greg Williamson
DBA
GlobeXplorer LLC


-----Original Message-----
From:	postgis-users-bounces at postgis.refractions.net on behalf of Jeff Hoffmann
Sent:	Sun 10/1/2006 3:17 PM
To:	PostGIS Users Discussion
Cc:	
Subject:	Re: [postgis-users] Newcomer-performance issue

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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


-------------------------------------------------------
Click link below if it is SPAM gsw at globexplorer.com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45203d4f93151076418835&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:45203d4f93151076418835!
-------------------------------------------------------