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