<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">Hi guys <br><br>I hav<font size="3">e a query here that joins about 10 tables tables and uses two distance queries. Im am having massive performance issues with it and im hoping you could help me to tune it or cha</font>nge it so that performance is increased. All of the columns being used for the joins have indexes and I have performed ANALYZE and VACUUM on each table. The query is as follows:<br><br><font size="2">SELECT a.service_id, f.service_id, j.service_id, (k.arrival_time - a.depart_time) AS time<br>FROM service1 a, service1 e, bus_stops1 c, stop_link d, service1 f, stop_link h, service1 i, service1 j, service1 k, bus_stops1 l<br>WHERE c.east_north &&<br> setsrid(box3d(expand(PointFromText('POINT(318475 673980)',27700),400)),27700)<br>AND l.east_north
&&<br> setsrid(box3d(expand(PointFromText('POINT(326983.487899314 671898.041182569)',27700),400)),27700)<br>AND distance(PointFromText('POINT(326983.487899314 671898.041182569)', 27700),l.east_north) < 200<br>AND distance(PointFromText('POINT(318475 673980)', 27700),c.east_north) < 200<br>AND a.depart_time BETWEEN '13:50' - interval '5minutes' AND '13:50' + interval '5 minutes'<br>AND f.depart_time BETWEEN e.depart_time + interval '3minutes' AND e.depart_time + interval '13 minutes'<br>AND j.depart_time BETWEEN i.depart_time + interval '3minutes' AND i.depart_time + interval '13 minutes'<br>AND a.stop_reference = c.stop_reference<br>AND e.service_id = a.service_id<br>AND d.stop_a = e.stop_reference<br>AND d.stop_b = f.stop_reference<br>AND f.service_id = i.service_id<br>AND h.stop_a = i.stop_reference<br>AND h.stop_b = j.stop_reference<br>AND j.service_id = k.service_id<br>AND k.stop_reference = l.stop_reference<br>AND
a.stop_order < e.stop_order<br>AND f.stop_order < i.stop_order<br>AND j.stop_order < k.stop_order<br>ORDER BY time, a.depart_time<br>LIMIT 1;</font><br><br><br>This is the EXPLAIN ANALYZE of this query:<br><br> <font size="2">Limit (cost=103086.88..103086.88 rows=1 width=28) (actual time=390489.993..390489.997 rows=1 loops=1)<br> -> Sort (cost=103086.88..103316.50 rows=91849 width=28) (actual time=390489.985..390489.985 rows=1 loops=1)<br> Sort Key: (k.arrival_time - a.depart_time), a.depart_time<br> -> Merge Join (cost=74721.84..93887.80 rows=91849 width=28) (actual time=74200.285..380077.222 rows=417915 loops=1)<br> Merge Cond: ("outer"."?column6?" =
"inner"."?column5?")<br> Join Filter: (("inner".depart_time >= ("outer".depart_time + '00:03:00'::interval)) AND ("inner".depart_time <= ("outer".depart_time + '00:13:00'::interval)))<br> -> Sort (cost=63214.89..63583.86 rows=147585 width=38) (actual time=58256.350..59157.265 rows=233571 loops=1)<br> Sort Key: (i.stop_reference)::text<br> -> Nested Loop (cost=2528.29..45913.84 rows=147585 width=38) (actual time=20839.047..50211.661 rows=242984
loops=1)<br> Join Filter: ("outer".stop_order < "inner".stop_order)<br> -> Hash Join (cost=2528.29..21682.94 rows=3180 width=20) (actual time=20838.982..37804.115 rows=8093 loops=1)<br> Hash Cond: (("outer".stop_reference)::text =
("inner".stop_b)::text)<br> Join Filter: (("outer".depart_time >= ("inner".depart_time + '00:03:00'::interval)) AND ("outer".depart_time <= ("inner".depart_time + '00:13:00<br>'::interval)))<br> -> Seq Scan on service1 f (cost=0.00..14990.58 rows=718258 width=30) (actual time=0.043..14325.330 rows=718258 loops=1)<br> -> Hash (cost=2527.53..2527.53
rows=306 width=33) (actual time=20602.792..20602.792 rows=1462 loops=1)<br> -> Merge Join (cost=1493.57..2527.53 rows=306 width=33) (actual time=19423.825..20596.730 rows=1462 loops=1)<br> Merge Cond: (("outer".stop_a)::text =
"inner"."?column5?")<br> -> Index Scan using link_stop_a on stop_link d (cost=0.00..2429.39 rows=49872 width=26) (actual time=0.054..20490.401 rows=20935 l<br>oops=1)<br> -> Sort (cost=1493.57..1493.68 rows=46 width=34) (actual time=10.635..15.345 rows=1464
loops=1)<br> Sort Key: (e.stop_reference)::text<br> -> Nested Loop (cost=8.59..1492.30 rows=46 width=34) (actual time=0.921..8.670 rows=235
loops=1)<br> Join Filter: ("outer".stop_order < "inner".stop_order)<br> -> Nested Loop (cost=8.59..1484.68 rows=1 width=16) (actual time=0.856..4.633 rows=7
loops=1)<br> -> Index Scan using stops_distance1 on bus_stops1 c (cost=0.00..14.04 rows=1 width=13) (actual time=0.222..0.391<br> rows=3 loops=1)<br> Index Cond:
(east_north && '0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913<br>410000000098942441000000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry)<br> Filter: ((east_north && '0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913410<br>000000098942441000000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry) AND
(distance('0101000020346C0000000000002C7013410000<br>000078912441'::geometry, east_north) < 200::double precision))<br> -> Bitmap Heap Scan on service1 a (cost=8.59..1464.97 rows=453 width=30) (actual time=0.454..1.384 rows=2
loops=<br>3)<br> Recheck Cond: ((a.stop_reference)::text = ("outer".stop_reference)::text)<br> Filter: (((depart_time)::interval >=
'13:45:00'::interval) AND ((depart_time)::interval <= '13:55:00'::inter<br>val))<br> -> Bitmap Index Scan on service1_stop_reference (cost=0.00..8.59 rows=453 width=0) (actual time=0.135..0.1<br>35 rows=213
loops=3)<br> Index Cond: ((a.stop_reference)::text = ("outer".stop_reference)::text)<br> -> Index Scan using service1_service_id on service1 e (cost=0.00..5.71 rows=127
width=30) (actual time=0.022..0.249 ro<br>ws=62 loops=7)<br> Index Cond: (e.service_id = "outer".service_id)<br> -> Index Scan using service1_service_id on service1 i (cost=0.00..5.71 rows=127 width=30) (actual time=0.755..1.212 rows=61
loops=8093)<br> Index Cond: ("outer".service_id = i.service_id)<br> -> Sort (cost=11506.95..11612.00 rows=42022 width=33) (actual time=15890.055..135847.222 rows=33805165 loops=1)<br> Sort Key: (h.stop_a)::text<br> -> Merge Join (cost=5908.77..7568.48 rows=42022 width=33) (actual time=5842.981..9827.070 rows=209740
loops=1)<br> Merge Cond: (("outer".stop_b)::text = "inner"."?column5?")<br> -> Index Scan using link_stop_b on stop_link h (cost=0.00..2429.52 rows=49872 width=26) (actual time=219.860..4054.111 rows=19252 loops=1)<br> -> Sort (cost=5908.77..5924.94 rows=6466 width=34) (actual time=3098.791..3864.662 rows=210487
loops=1)<br> Sort Key: (j.stop_reference)::text<br> -> Nested Loop (cost=8.59..5499.52 rows=6466 width=34) (actual time=417.778..1919.171 rows=44866 loops=1)<br> Join Filter: ("inner".stop_order <
"outer".stop_order)<br> -> Nested Loop (cost=8.59..4440.37 rows=139 width=16) (actual time=386.184..412.717 rows=1907 loops=1)<br> -> Index Scan using stops_distance1 on bus_stops1 l (cost=0.00..42.07 rows=3 width=13) (actual time=302.551..302.750 rows=3
loops=<br>1)<br> Index Cond: (east_north && '0103000020346C00000100000005000000000000E0DDEE134100000000147E2441000000E0DDEE13410000002054842441<br>000000005EFB13410000002054842441000000005EFB134100000000147E2441000000E0DDEE134100000000147E2441'::geometry)<br> Filter: ((east_north &&
'0103000020346C00000100000005000000000000E0DDEE134100000000147E2441000000E0DDEE13410000002054842441000<br>000005EFB13410000002054842441000000005EFB134100000000147E2441000000E0DDEE134100000000147E2441'::geometry) AND (distance('0101000020346C0000B5E09BF31DF51341B6E1151534812441'::geo<br>metry, east_north) < 200::double precision))<br> -> Bitmap Heap Scan on service1 k (cost=8.59..1460.44 rows=453 width=30) (actual time=28.171..32.343 rows=636
loops=3)<br> Recheck Cond: ((k.stop_reference)::text = ("outer".stop_reference)::text)<br> -> Bitmap Index Scan on service1_stop_reference (cost=0.00..8.59 rows=453 width=0) (actual time=28.038..28.038 rows=636
loop<br>s=3)<br> Index Cond: ((k.stop_reference)::text = ("outer".stop_reference)::text)<br> -> Index Scan using service1_service_id on service1 j (cost=0.00..5.71 rows=127 width=30) (actual time=0.043..0.516 rows=55
loops=1907)<br> Index Cond: (j.service_id = "outer".service_id)<br> Total runtime: 390871.465 ms</font><br><br><br>As you can see it is taking entirely too much time for a query of this size in my opinion. Therefore im assuming I have done something wrong or could improve it in some way. I would really appreciate your help.<br><br>Sincerely<br><br>Alan<br><br><br><br></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>