<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] Massive Performance Issues</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Also -- probably you've already done it -- make sure you've run an ANALYZE against all of these tables so the planner "knows" what is in them. If there are skewed distributions increasing the stats target for the effected tables/columns (see ALTER TABLE for details) as the default might not be sufficient.<BR>
<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
GlobeXplorer LLC, a DigitalGlobe company<BR>
<BR>
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.<BR>
<BR>
(My corporate masters made me say this.)<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Alan Cunnane<BR>
Sent: Tue 7/17/2007 7:26 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: Re: [postgis-users] Massive Performance Issues<BR>
<BR>
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>
----- 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>
* 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 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 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 HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A 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! Mail is the world's favourite email. Don't settle for less, sign up for<BR>
your free account today <A HREF="http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter07.html">http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter07.html</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>