<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;">Ah I am sorry here is the eplain analyze requested. The explain analyze query is shown below:<br><br> QUERY PLAN<br><br>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>---------------------------------------------------<br> Limit (cost=150061918.96..150061918.97 rows=1 width=166) (actual time=71135.765..71135.765 rows=0 loops=1)<br> -> Sort (cost=150061918.96..150407602.50
rows=138273415 width=166) (actual time=71135.758..71135.758 rows=0 loops=1)<br> Sort Key: (e.arrival_time - g.depart_time)<br> -> Merge Join (cost=665925.37..32125314.72 rows=138273415 width=166) (actual time=71135.734..71135.734 rows=0 loops=1)<br> Merge Cond: ("outer"."?column8?" = "inner"."?column7?")<br> Join Filter: (("outer".depart_time >= ("inner".depart_time + '00:03:00'::interval)) AND ("outer".depart_time <= ("inner".depart_time + '00:13:00'::interval)))<br> -> Sort (cost=441268.22..442374.55 rows=442532 width=115) (actual time=70772.175..70772.175 rows=1
loops=1)<br> Sort Key: (c.stop_reference)::text<br> -> Hash Join (cost=49458.47..324978.85 rows=442532 width=115) (actual time=23959.230..55506.743 rows=194484 loops=1)<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) (actual time=20936.503..47681.549 rows=1436516 loops=1)<br> -> Hash (cost=49458.01..49458.01 rows=185 width=77) (actual time=1250.002..1250.002 rows=3168 loops=1)<br> -> Nested Loop (cost=54.14..49458.01 rows=185 width=77) (actual time=352.784..1231.899 rows=3168
loops=1)<br> Join Filter: (("outer".route_number)::text <> ("inner".route_number)::text)<br> -> Index Scan using routes1_pkey on routes1 h (cost=0.00..6.00 rows=1 width=24) (actual time=47.059..47.064 rows=1 loops=1)<br> Index Cond: (6867 =
service_id)<br> -> Nested Loop (cost=54.14..49449.68 rows=186 width=53) (actual time=305.686..1160.476 rows=3168 loops=1)<br> -> Nested Loop (cost=54.14..48330.43 rows=186 width=29) (actual time=88.261..798.839 rows=3168
loops=1)<br> -> Index Scan using stops_distance1 on bus_stops1 d (cost=0.00..21.94 rows=2 width=13) (actual time=24.860..40.809 rows=3 lo<br>ops=1)<br> Index Cond: (east_north &&
'0103000020346C0000010000000500000000000000D42E1441000000001C82244100000000D42E1441000000005C<br>88244100000000543B1441000000005C88244100000000543B1441000000001C82244100000000D42E1441000000001C822441'::geometry)<br> Filter: ((east_north && '0103000020346C0000010000000500000000000000D42E1441000000001C82244100000000D42E1441000000005C882<br>44100000000543B1441000000005C88244100000000543B1441000000001C82244100000000D42E1441000000001C822441'::geometry) AND (distance('0101000020346C00000000000014351441000000003C852441<br>'::geometry, east_north) < 200::double
precision))<br> -> Bitmap Heap Scan on service1 e (cost=54.14..24064.46 rows=7183 width=58) (actual time=35.794..244.812 rows=1056 loops=3)<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) (actual time=16.058..16.058 rows=<br>1056 loops=3)<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) (actual time=0.095..0.100 rows=1 loops=3168)<br> Index Cond: (i.service_id = "outer".service_id)<br> -> Sort (cost=224657.14..226063.21 rows=562427 width=117) (actual time=363.545..363.545 rows=0
loops=1)<br> Sort Key: (b.stop_b)::text<br> -> Merge Join (cost=12136.48..23011.69 rows=562427 width=117) (actual time=363.523..363.523 rows=0 loops=1)<br> Merge Cond: (("outer".stop_a)::text = "inner"."?column6?")<br> -> Index Scan using link_stop_a on stop_link b (cost=0.00..2312.36 rows=50578 width=84) (actual time=24.077..60.377 rows=7213
loops=1)<br> -> Sort (cost=12136.48..12142.04 rows=2224 width=75) (actual time=271.807..271.822 rows=4 loops=1)<br> Sort Key: (a.stop_reference)::text<br> -> Nested Loop (cost=2864.85..12012.84 rows=2224 width=75) (actual time=242.716..271.730 rows=4
loops=1)<br> -> Nested Loop (cost=96.53..501.68 rows=1 width=29) (actual time=147.867..147.927 rows=2 loops=1)<br> -> Index Scan using stops_distance1 on bus_stops1 f (cost=0.00..21.94 rows=2 width=13) (actual time=23.857..34.496 rows=3
loops=1)<br> Index Cond: (east_north && '0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913410000000098942441<br>000000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry)<br> Filter: ((east_north && '0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913410000000098942441000<br>000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry) AND (distance('0101000020346C0000000000002C7013410000000078912441'::geo<br>metry, east_north) < 200::double
precision))<br> -> Bitmap Heap Scan on service1 g (cost=96.53..239.42 rows=36 width=58) (actual time=37.784..37.787 rows=1 loops=3)<br> Recheck Cond: ((6867 = g.service_id) AND (("outer".stop_reference)::text =
(g.stop_reference)::text))<br> -> BitmapAnd (cost=96.53..96.53 rows=36 width=0) (actual time=37.766..37.766 rows=0 loops=3)<br> -> Bitmap Index Scan on service1_service_id (cost=0.00..42.14 rows=7183 width=0) (actual time=21.129..21.129
rows=130<br>loops=3)<br> Index Cond: (6867 = service_id)<br> -> Bitmap Index Scan on service1_stop_reference (cost=0.00..54.14 rows=7183 width=0) (actual time=16.621..16.621 rows=<br>426
loops=3)<br> Index Cond: (("outer".stop_reference)::text = (g.stop_reference)::text)<br> -> Bitmap Heap Scan on service1 a (cost=2768.32..11481.24 rows=2394 width=58) (actual time=61.841..61.864 rows=2
loops=2)<br> Recheck Cond: ((a.service_id = 6867) AND ("outer".stop_order < a.stop_order))<br> -> BitmapAnd (cost=2768.32..2768.32 rows=2394 width=0) (actual time=61.828..61.828 rows=0
loops=2)<br> -> Bitmap Index Scan on service1_service_id (cost=0.00..42.14 rows=7183 width=0) (actual time=0.055..0.055 rows=130 loops=2)<br> Index Cond: (service_id =
6867)<br> -> Bitmap Index Scan on service1_stop_order (cost=0.00..2725.94 rows=478839 width=0) (actual time=61.669..61.669 rows=79104<br>loops=2)<br> Index Cond: ("outer".stop_order < a.stop_order)<br> Total runtime: 71155.967 ms<br>(55 rows)<br><br>At the moment it is not returning any rows, however this could be because
there are no two routes which intersect at this time. That can be worked out later but the performance of the query still needs to improve. The bounding box seems to make a massive difference and I am now wondering can I use it elsewhere too. For example to create the stop_link table I need to create a table from a query which selects all stops on different routes within 200m of each other. Can I use a bounding box to do this? At the moment this is my query which takes a long time to complete:<br><br>CREATE table stops_link (stop_a, stop_b)<br>AS SELECT DISTINCT a.stop_reference, b.stop_reference <br>FROM bus_stops1 a, bus_stops1 b, service1 c, service1 d<br>WHERE distance(a.east_north, b.east_north) < 200<br>AND expand(a.east_north, 200) && b.east_north<br>AND a.stop_reference = c.stop_reference<br>AND b.stop_reference = d.stop_reference<br>AND c.service_id != d.service_id<br>AND a.stop_reference != b.stop_reference;<br><br>Sorry if it seems im bombarding you
with questions now its just you have been so helpful so far! <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 2:04:17 AM<br>Subject: Re: [postgis-users] Massive Performance Issues<br><br><div>* Alan Cunnane (alan_cunnane@yahoo.co.uk) wrote:<br>> Already the bounding box seems to be making a big difference as you can see. This is th eentire EXPLAIN output:<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>Can you run the query with an 'explain analyze' so we can see where the<br>time is actually being spent?<br><br>Is the query generating the correct
result?<br><br> Thanks,<br><br> Stephen<br><br>> ----- 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>> * 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>> <br>> _______________________________________________<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>> <br>> <br>> <br>> <br>> <br>> <br>> <br>> ___________________________________________________________<br>> Yahoo! Answers - Got a question? Someone out there knows the answer. Try it<br>> now.<br>> <a target="_blank" href="http://uk.answers.yahoo.com/">http://uk.answers.yahoo.com/</a> <br>> _______________________________________________<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><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! Answers - Get better answers from someone who knows. <a
href="http://uk.answers.yahoo.com/;_ylc=X3oDMTEydmViNG02BF9TAzIxMTQ3MTcxOTAEc2VjA21haWwEc2xrA3RhZ2xpbmU">Try
it now</a>.</body></html>