[postgis-users] Massive Performance Issues

[Alan Cunnane]

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  
ORDER BY time
LIMIT 1;

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
 width=166)
         Sort Key: (e.arrival_time - g.depart_time)
         ->  Merge Join  (cost=319647.74..50651680879617.87 rows=918636483413506
 width=166)
               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.

Thanks
:





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