Already the bounding box seems to be making a big difference as you can see.
This is th eentire EXPLAIN output:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=300504226.75..300504226.75 rows=1 width=166)
-> Sort (cost=300504226.75..300853402.66 rows=139670363 width=166)
Sort Key: (e.arrival_time - g.depart_time)
-> Merge Join (cost=178572130.36..181365916.96 rows=139670363
width=166)
Merge Cond: (("outer"."?column3?" = "inner"."?column9?") AND
("outer"."?column4?" = "inner"."?column10?"))
-> Sort (cost=6729.56..6856.00 rows=50578 width=84)
Sort Key: (b.stop_b)::text, (b.stop_a)::text
-> Seq Scan on stop_link b (cost=0.00..896.78 rows=50578
width=84)
-> Sort (cost=178565400.80..178841549.25 rows=110459380
width=166)
Sort Key: (c.stop_reference)::text,
(a.stop_reference)::text
-> Merge Join (cost=876050.42..84532802.69
rows=110459380 width=166)
Merge Cond: ("outer".service_id = "inner".service_id)
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))
-> Nested Loop (cost=311480.75..1849384.74
rows=41103350 width=144)
Join Filter: (("outer".route_number)::text <>
("inner".route_number)::text)
-> Merge Join (cost=48337.45..49601.51
rows=186 width=53)
Merge Cond: ("outer".service_id =
"inner".service_id)
-> Index Scan using routes1_pkey on
routes1 h (cost=0.00..1211.78 rows=19798 width=24)
-> Sort (cost=48337.45..48337.91
rows=186 width=29)
Sort Key: g.service_id
-> Nested Loop
(cost=54.14..48330.43 rows=186 width=29)
-> Index Scan using
stops_distance1 on bus_stops1 f (cost=0.00..21.94 rows=2 width=13)
Index Cond:
(east_north &&
'0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913410000000098942441000000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry)
Filter: ((east_north
&&
'0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913410000000098942441000000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry)
AND (distance('0101000020346C0000000000002C7013410000000078912441'::geometry,
east_north) < 200::double precision))
-> Bitmap Heap Scan on
service1 g (cost=54.14..24064.46 rows=7183 width=58)
Recheck Cond:
(("outer".stop_reference)::text = (g.stop_reference)::text)
-> Bitmap Index Scan
on service1_stop_reference (cost=0.00..54.14 rows=7183 width=0)
Index Cond:
(("outer".stop_reference)::text = (g.stop_reference)::text)
-> Materialize (cost=263143.30..268625.08
rows=222378 width=91)
-> Hash Join (cost=24724.11..259662.92
rows=222378 width=91)
Hash Cond: ("outer".service_id =
"inner".service_id)
Join Filter: ("outer".stop_order <
"inner".stop_order)
-> Seq Scan on service1 c
(cost=0.00..190676.16 rows=1436516 width=58)
-> Hash (cost=24723.88..24723.88
rows=93 width=53)
-> Nested Loop
(cost=54.14..24723.88 rows=93 width=53)
-> Nested Loop
(cost=54.14..24164.26 rows=93 width=29)
-> Index Scan
using stops_distance1 on bus_stops1 d (cost=0.00..10.01 rows=1 width=13)
Index
Cond: (east_north &&
'0103000020346C0000010000000500000000000000FC38144100000000D244244100000000FC38144100000000124B2441000000007C45144100000000124B2441000000007C45144100000000D244244100000000FC38144100000000D2442441'::geometry)
Filter:
((east_north &&
'0103000020346C0000010000000500000000000000FC38144100000000D244244100000000FC38144100000000124B2441000000007C45144100000000124B2441000000007C45144100000000D244244100000000FC38144100000000D2442441'::geometry)
AND (distance('0101000020346C0000000000003C3F144100000000F2472441'::geometry,
east_north) < 200::double precision))
-> Bitmap Heap
Scan on service1 e (cost=54.14..24064.46 rows=7183 width=58)
Recheck
Cond: (("outer".stop_reference)::text = (e.stop_reference)::text)
-> Bitmap
Index Scan on service1_stop_reference (cost=0.00..54.14 rows=7183 width=0)
Index Cond: (("outer".stop_reference)::text = (e.stop_reference)::text)
-> Index Scan using
routes1_pkey on routes1 i (cost=0.00..6.00 rows=1 width=24)
Index Cond:
(i.service_id = "outer".service_id)
-> Sort (cost=564569.68..568160.97 rows=1436516
width=58)
Sort Key: a.service_id
-> Seq Scan on service1 a
(cost=0.00..190676.16 rows=1436516 width=58)
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
----- Original Message ----
From: Stephen Frost <[EMAIL PROTECTED]>
To: PostGIS Users Discussion <[email protected]>
Sent: Wednesday, 18 July, 2007 1:33:36 AM
Subject: Re: [postgis-users] Massive Performance Issues
* Alan Cunnane ([EMAIL PROTECTED]) wrote:
> Hi Steven as requested here are the table definitions and constraints:
[...]
> 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
Have you tried the bounding box addition I suggested? Also, what about
the whole explain analyze of the query? Perferrably with the bounding
box included?
Thanks,
Stephen
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
___________________________________________________________
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
