[postgis-users] Massive Performance Issues
sfrost at snowman.net
Tue Jul 17 17:17:37 PDT 2007
* 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
WHERE f.east_north &&
distance(PointFromText('POINT(318475 673980)', 27700),f.east_north) < 200
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...
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 189 bytes
Desc: Digital signature
More information about the postgis-users