[postgis-users] Massive Performance Issues
Alan Cunnane
alan_cunnane at yahoo.co.uk
Tue Jul 17 17:44:35 PDT 2007
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 <sfrost at snowman.net>
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Sent: Wednesday, 18 July, 2007 1:33:36 AM
Subject: Re: [postgis-users] Massive Performance Issues
* Alan Cunnane (alan_cunnane at yahoo.co.uk) 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
postgis-users at postgis.refractions.net
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/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070718/149a8778/attachment.html>
More information about the postgis-users
mailing list