[postgis-users] Massive Performance Issues

[Stephen Frost]

* Alan Cunnane (alan_cunnane at yahoo.co.uk) wrote:
> SELECT f.stop_reference AS origstop, a.stop_reference AS origconnect, h.route_number, c.stop_reference AS destconnect, i.route_number, d.stop_reference AS deststop, (e.arrival_time - g.depart_time) AS time 
> FROM service1 a, stop_link b, service1 c, bus_stops1 d, service1 e, bus_stops1 f, service1 g, routes1 h, routes1 i 

In general I find that it's much clearer to use JOIN syntax rather than
doing a cartesian join and then limiting it using where clauses.  It can
also help identify joins which aren't being properly constrained.

> WHERE distance(PointFromText('POINT(318475 673980)', 27700),f.east_north) < 200 
> AND distance(PointFromText('POINT(331727 664569)', 27700),d.east_north) < 200 

You should include a bounding box restriction before doing distance
calculations to limit the amount of data being run through the distance
function, ie:

WHERE f.east_north && 
      setsrid(box3d(expand(PointFromText('POINT(318475 673980)',27700),400)),27700)
	  AND
	  distance(PointFromText('POINT(318475 673980)', 27700),f.east_north) < 200
	  AND
	  d.east_north &&
      setsrid(box3d(expand(PointFromText('POINT(331727 664569)',27700),400)),27700)
	  AND
	  distance(PointFromText('POINT(331727 664569)', 27700),d.east_north) < 200

> I have limited the query to just one result and have indexes built on all the columns that are being linked however this has not helped the performance at all. In fact when I run an EXPLAIN the cost is massive! See below:

Seeing the rest of it would certainly help, along with your table
definitions and foreign-key constraints and indexes...

	Thanks,

		Stephen
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070717/8c079146/attachment.pgp>