[postgis-users] Massive Performance Issues

Alan Cunnane alan_cunnane at yahoo.co.uk
Tue Jul 17 17:03:18 PDT 2007

Hi guys 

I have a few tables that contain bus timetable data and location data about each bus stop. These are quite large tables with over 15,000 stops, a service table with 700,000 rows etc. Now I have a query which finds all the routes that get you from a certain location to a certain destination using two routes. This query links these tables and uses aliases to join the tables together. The query is below:

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 
WHERE distance(PointFromText('POINT(318475 673980)', 27700),f.east_north) < 200 
AND distance(PointFromText('POINT(331727 664569)', 27700),d.east_north) < 200 
AND c.depart_time BETWEEN a.depart_time + interval '3minutes' AND a.depart_time + interval '13 minutes' 
AND g.stop_order < a.stop_order 
AND c.stop_order < e.stop_order 
AND f.stop_reference = g.stop_reference 
AND g.service_id = a.service_id 
AND d.stop_reference = e.stop_reference 
AND e.service_id = c.service_id 
AND a.stop_reference = b.stop_a 
AND c.stop_reference = b.stop_b 
AND h.service_id = a.service_id 
AND i.service_id = c.service_id 
AND h.route_number != i.route_number
AND a.service_id = 162  

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:

Limit  (cost=1927394811121669.25..1927394811121669.25 rows=1 width=166)
   ->  Sort  (cost=1927394811121669.25..1929691402330203.00 rows=918636483413506
         Sort Key: (e.arrival_time - g.depart_time)
         ->  Merge Join  (cost=319647.74..50651680879617.87 rows=918636483413506
               Merge Cond: ("outer".service_id = "inner".service_id)
               Join Filter: ("outer".stop_order < "inner".stop_order)
               ->  Nested Loop  (cost=24822.53..123766489560.30 rows=11511550111
15 width=157)
                     Join Filter: ("inner".stop_order < "outer".stop_order)
                     ->  Nested Loop  (cost=48.16..46069289504.54 rows=144244268
6 width=144)
                           ->  Nested Loop  (cost=42.14..34615851450.83 rows=114
0766884 width=144)
                                 Join Filter: (("outer".depart_time >= ("inner".
depart_time + '00:03:00'::interval)) AND ("outer".depart_time <= ("inner".depart..................................................etc

Please tell me what can I do to improve this as this seems an extraordinary amount of time for this query! I really need your help.


Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070718/03907772/attachment.html>

More information about the postgis-users mailing list