<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><div>Hi guys <br><br>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:<br><br>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 <br>FROM service1 a, stop_link b, service1 c, bus_stops1 d, service1 e, bus_stops1 f, service1 g, routes1 h, routes1 i <br>WHERE distance(PointFromText('POINT(318475 673980)',
27700),f.east_north) < 200 <br>AND distance(PointFromText('POINT(331727 664569)', 27700),d.east_north) < 200 <br>AND c.depart_time BETWEEN a.depart_time + interval '3minutes' AND a.depart_time + interval '13 minutes' <br>AND g.stop_order < a.stop_order <br>AND c.stop_order < e.stop_order <br>AND f.stop_reference = g.stop_reference <br>AND g.service_id = a.service_id <br>AND d.stop_reference = e.stop_reference <br>AND e.service_id = c.service_id <br>AND a.stop_reference = b.stop_a <br>AND c.stop_reference = b.stop_b <br>AND h.service_id = a.service_id <br>AND i.service_id = c.service_id <br>AND h.route_number != i.route_number<br>AND a.service_id = 162 <br>ORDER BY time<br>LIMIT 1;<br><br>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:<br><br><br>Limit
(cost=1927394811121669.25..1927394811121669.25 rows=1 width=166)<br> -> Sort (cost=1927394811121669.25..1929691402330203.00 rows=918636483413506<br> width=166)<br> Sort Key: (e.arrival_time - g.depart_time)<br> -> Merge Join (cost=319647.74..50651680879617.87 rows=918636483413506<br> width=166)<br> Merge Cond: ("outer".service_id = "inner".service_id)<br> Join Filter: ("outer".stop_order < "inner".stop_order)<br> -> Nested Loop (cost=24822.53..123766489560.30 rows=11511550111<br>15
width=157)<br> Join Filter: ("inner".stop_order < "outer".stop_order)<br> -> Nested Loop (cost=48.16..46069289504.54 rows=144244268<br>6 width=144)<br> -> Nested Loop (cost=42.14..34615851450.83 rows=114<br>0766884 width=144)<br> Join Filter: (("outer".depart_time >= ("inner".<br>depart_time + '00:03:00'::interval)) AND ("outer".depart_time <=
("inner".depart..................................................etc<br><br><br><br><br>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.<br><br>Thanks<br>:<br><br></div></div><br>
<hr size=1>
Yahoo! Mail is the world's favourite email. Don't settle for less, <a
href="http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter07.html">sign up for your free
account today</a>.</body></html>