<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 style="font-family: times new roman,new york,times,serif; font-size: 12pt;">Already the bounding box seems to be making a big difference as you can see. This is th eentire EXPLAIN output:<br><br> QUERY
PLAN
<br>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br> Limit (cost=300504226.75..300504226.75 rows=1 width=166)<br> -> Sort (cost=300504226.75..300853402.66 rows=139670363 width=166)<br> Sort Key: (e.arrival_time - g.depart_time)<br> -> Merge Join (cost=178572130.36..181365916.96 rows=139670363 width=166)<br> Merge Cond: (("outer"."?column3?" = "inner"."?column9?") AND
("outer"."?column4?" = "inner"."?column10?"))<br> -> Sort (cost=6729.56..6856.00 rows=50578 width=84)<br> Sort Key: (b.stop_b)::text, (b.stop_a)::text<br> -> Seq Scan on stop_link b (cost=0.00..896.78 rows=50578 width=84)<br> -> Sort (cost=178565400.80..178841549.25 rows=110459380 width=166)<br> Sort Key: (c.stop_reference)::text,
(a.stop_reference)::text<br> -> Merge Join (cost=876050.42..84532802.69 rows=110459380 width=166)<br> Merge Cond: ("outer".service_id = "inner".service_id)<br> Join Filter: (("outer".depart_time >= ("inner".depart_time + '00:03:00'::interval)) AND ("outer".depart_time <= ("inner".depart_time + '00:13:00'::interval)) AND ("outer".stop_order < "inner".stop_order))<br>
-> Nested Loop (cost=311480.75..1849384.74 rows=41103350 width=144)<br> Join Filter: (("outer".route_number)::text <> ("inner".route_number)::text)<br> -> Merge Join (cost=48337.45..49601.51 rows=186 width=53)<br> Merge Cond: ("outer".service_id =
"inner".service_id)<br> -> Index Scan using routes1_pkey on routes1 h (cost=0.00..1211.78 rows=19798 width=24)<br> -> Sort (cost=48337.45..48337.91 rows=186 width=29)<br> Sort Key:
g.service_id<br> -> Nested Loop (cost=54.14..48330.43 rows=186 width=29)<br> -> Index Scan using stops_distance1 on bus_stops1 f (cost=0.00..21.94 rows=2
width=13)<br> Index Cond: (east_north && '0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913410000000098942441000000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry)<br> Filter: ((east_north &&
'0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913410000000098942441000000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry) AND (distance('0101000020346C0000000000002C7013410000000078912441'::geometry, east_north) < 200::double precision))<br> -> Bitmap Heap Scan on service1 g (cost=54.14..24064.46 rows=7183
width=58)<br> Recheck Cond: (("outer".stop_reference)::text = (g.stop_reference)::text)<br> -> Bitmap Index Scan on service1_stop_reference (cost=0.00..54.14 rows=7183
width=0)<br> Index Cond: (("outer".stop_reference)::text = (g.stop_reference)::text)<br> -> Materialize (cost=263143.30..268625.08 rows=222378 width=91)<br> -> Hash
Join (cost=24724.11..259662.92 rows=222378 width=91)<br> Hash Cond: ("outer".service_id = "inner".service_id)<br> Join Filter: ("outer".stop_order < "inner".stop_order)<br> ->
Seq Scan on service1 c (cost=0.00..190676.16 rows=1436516 width=58)<br> -> Hash (cost=24723.88..24723.88 rows=93 width=53)<br> -> Nested Loop (cost=54.14..24723.88 rows=93
width=53)<br> -> Nested Loop (cost=54.14..24164.26 rows=93 width=29)<br> -> Index Scan using stops_distance1 on bus_stops1 d (cost=0.00..10.01 rows=1
width=13)<br> Index Cond: (east_north &&
'0103000020346C0000010000000500000000000000FC38144100000000D244244100000000FC38144100000000124B2441000000007C45144100000000124B2441000000007C45144100000000D244244100000000FC38144100000000D2442441'::geometry)<br> Filter: ((east_north && '0103000020346C0000010000000500000000000000FC38144100000000D244244100000000FC38144100000000124B2441000000007C45144100000000124B2441000000007C45144100000000D244244100000000FC38144100000000D2442441'::geometry) AND (distance('0101000020346C0000000000003C3F144100000000F2472441'::geometry, east_north) < 200::double
precision))<br> -> Bitmap Heap Scan on service1 e (cost=54.14..24064.46 rows=7183 width=58)<br> Recheck Cond: (("outer".stop_reference)::text =
(e.stop_reference)::text)<br> -> Bitmap Index Scan on service1_stop_reference (cost=0.00..54.14 rows=7183 width=0)<br>
Index Cond: (("outer".stop_reference)::text = (e.stop_reference)::text)<br> -> Index Scan using routes1_pkey on routes1 i (cost=0.00..6.00 rows=1 width=24)<br> Index Cond: (i.service_id =
"outer".service_id)<br> -> Sort (cost=564569.68..568160.97 rows=1436516 width=58)<br> Sort Key: a.service_id<br> -> Seq Scan on service1 a (cost=0.00..190676.16 rows=1436516 width=58)<br><br><br>Can you see anymore clues in here which would help as the performance still really needs to be a lot better than this? I really appreciate your help<br><br><br><br><br><div style="font-family: times new roman,new
york,times,serif; font-size: 12pt;">----- Original Message ----<br>From: Stephen Frost <sfrost@snowman.net><br>To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>Sent: Wednesday, 18 July, 2007 1:33:36 AM<br>Subject: Re: [postgis-users] Massive Performance Issues<br><br><div>* Alan Cunnane (alan_cunnane@yahoo.co.uk) wrote:<br>> Hi Steven as requested here are the table definitions and constraints:<br>[...]<br>> Perhaps you can decipher what would be causing such a large performace problem from these constraints? Any help would be much appreciated as im at the end of my tether at this stage and really dont know what to do<br><br>Have you tried the bounding box addition I suggested? Also, what about<br>the whole explain analyze of the query? Perferrably with the bounding<br>box
included?<br><br> Thanks,<br><br> Stephen<br></div><div>_______________________________________________<br>postgis-users mailing list<br>postgis-users@postgis.refractions.net<br><a target="_blank" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></div><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>