<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<STYLE type=text/css>DIV {
MARGIN: 0px
}
</STYLE>
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=538173114-27072007><FONT face=Arial
color=#0000ff size=2>1) Not that it would make much of a difference in
your query aside from readability, but I believe your box3d setsrid calls are
a bit wasteful. Your expand function will already return an SRIDd
box.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=538173114-27072007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=538173114-27072007><FONT face=Arial
color=#0000ff size=2>So you can change</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=538173114-27072007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV><FONT size=2>l.east_north &&<BR>
setsrid(box3d(expand(PointFromText('POINT(326983.487899314
671898.041182569)',27700),400)),27700)</FONT><BR></DIV>
<DIV><SPAN class=538173114-27072007></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>t<SPAN
class=538173114-27072007>o</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=538173114-27072007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT><FONT><FONT size=2><SPAN class=538173114-27072007>l.east_north
&& expand(PointFromText('POINT(326983.487899314
671898.041182569)',27700),400)</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT><FONT><FONT size=2><SPAN
class=538173114-27072007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT><FONT><FONT size=2><SPAN class=538173114-27072007>Also makepoint is a
bit faster than pointfromtext, but since you are dealing with static points, I
guess that is not terribly important.</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT><FONT><FONT size=2><SPAN
class=538173114-27072007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT><FONT><FONT size=2><SPAN
class=538173114-27072007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT><FONT><FONT size=2><SPAN class=538173114-27072007>2) Why is your
expand area larger than your distance check. You are going thru probably
twice the number of records you need to since your distance call will never get
past the 200 expand mark</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT><FONT><FONT size=2><SPAN
class=538173114-27072007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT><FONT><FONT size=2><SPAN class=538173114-27072007>You should just be
doing </SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT><FONT><FONT size=2><SPAN
class=538173114-27072007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT><FONT><FONT size=2><SPAN class=538173114-27072007><SPAN
class=538173114-27072007>l.east_north &&
expand(PointFromText('POINT(326983.487899314
671898.041182569)',27700),200)</SPAN></SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT><FONT><FONT size=2><SPAN
class=538173114-27072007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT><FONT><FONT size=2><SPAN class=538173114-27072007>Hope that
helps,</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT><FONT><FONT size=2><SPAN
class=538173114-27072007>Regina</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT><FONT><FONT size=2><SPAN
class=538173114-27072007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT><FONT><FONT size=2><SPAN
class=538173114-27072007></SPAN></FONT></FONT></FONT> </DIV>
<DIV><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Alan
Cunnane<BR><B>Sent:</B> Wednesday, July 25, 2007 10:10 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> [postgis-users] Fw: Performance
Help<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV
style="FONT-SIZE: 10pt; FONT-FAMILY: times new roman, new york, times, serif">
<DIV
style="FONT-SIZE: 10pt; FONT-FAMILY: times new roman,new york,times,serif">Hi
guys <BR>
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman,new york,times,serif">
<DIV style="FONT-SIZE: 10pt; FONT-FAMILY: times new roman,new york,times,serif">
<DIV style="FONT-SIZE: 10pt; FONT-FAMILY: times new roman,new york,times,serif">
<DIV style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman,new york,times,serif">
<DIV
style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman,new york,times,serif"><BR>I
hav<FONT size=3>e a query here that joins about 10 tables tables and uses two
distance queries. Im am having massive performance issues with it and im hoping
you could help me to tune it or cha</FONT>nge it so that performance is
increased. All of the columns being used for the joins have indexes and I have
performed ANALYZE and VACUUM on each table. The query is as
follows:<BR><BR><FONT size=2>SELECT a.service_id, f.service_id, j.service_id,
(k.arrival_time - a.depart_time) AS time<BR>FROM service1 a, service1 e,
bus_stops1 c, stop_link d, service1 f, stop_link h, service1 i, service1 j,
service1 k, bus_stops1 l<BR>WHERE c.east_north &&<BR>
setsrid(box3d(expand(PointFromText('POINT(318475
673980)',27700),400)),27700)<BR>AND l.east_north
&&<BR>
setsrid(box3d(expand(PointFromText('POINT(326983.487899314
671898.041182569)',27700),400)),27700)<BR>AND
distance(PointFromText('POINT(326983.487899314 671898.041182569)',
27700),l.east_north) < 200<BR>AND distance(PointFromText('POINT(318475
673980)', 27700),c.east_north) < 200<BR>AND a.depart_time BETWEEN '13:50' -
interval '5minutes' AND '13:50' + interval '5 minutes'<BR>AND f.depart_time
BETWEEN e.depart_time + interval '3minutes' AND e.depart_time + interval '13
minutes'<BR>AND j.depart_time BETWEEN i.depart_time + interval '3minutes' AND
i.depart_time + interval '13 minutes'<BR>AND a.stop_reference =
c.stop_reference<BR>AND e.service_id = a.service_id<BR>AND d.stop_a =
e.stop_reference<BR>AND d.stop_b = f.stop_reference<BR>AND f.service_id =
i.service_id<BR>AND h.stop_a = i.stop_reference<BR>AND h.stop_b =
j.stop_reference<BR>AND j.service_id = k.service_id<BR>AND k.stop_reference =
l.stop_reference<BR>AND a.stop_order < e.stop_order<BR>AND f.stop_order <
i.stop_order<BR>AND j.stop_order < k.stop_order<BR>ORDER BY time,
a.depart_time<BR>LIMIT 1;</FONT><BR><BR><BR>This is the EXPLAIN ANALYZE of this
query:<BR><BR> <FONT size=2>Limit (cost=103086.88..103086.88 rows=1
width=28) (actual time=390489.993..390489.997 rows=1 loops=1)<BR>
-> Sort (cost=103086.88..103316.50 rows=91849 width=28) (actual
time=390489.985..390489.985 rows=1
loops=1)<BR> Sort Key:
(k.arrival_time - a.depart_time),
a.depart_time<BR> ->
Merge Join (cost=74721.84..93887.80 rows=91849 width=28) (actual
time=74200.285..380077.222 rows=417915
loops=1)<BR>
Merge Cond: ("outer"."?column6?" =
"inner"."?column5?")<BR>
Join Filter: (("inner".depart_time >= ("outer".depart_time +
'00:03:00'::interval)) AND ("inner".depart_time <= ("outer".depart_time +
'00:13:00'::interval)))<BR>
-> Sort (cost=63214.89..63583.86 rows=147585 width=38) (actual
time=58256.350..59157.265 rows=233571
loops=1)<BR>
Sort Key:
(i.stop_reference)::text<BR>
-> Nested Loop (cost=2528.29..45913.84 rows=147585 width=38)
(actual time=20839.047..50211.661 rows=242984
loops=1)<BR>
Join Filter: ("outer".stop_order <
"inner".stop_order)<BR>
-> Hash Join (cost=2528.29..21682.94 rows=3180 width=20) (actual
time=20838.982..37804.115 rows=8093
loops=1)<BR>
Hash Cond: (("outer".stop_reference)::text =
("inner".stop_b)::text)<BR>
Join Filter: (("outer".depart_time >= ("inner".depart_time +
'00:03:00'::interval)) AND ("outer".depart_time <= ("inner".depart_time +
'00:13:00<BR>'::interval)))<BR>
-> Seq Scan on service1 f (cost=0.00..14990.58 rows=718258
width=30) (actual time=0.043..14325.330 rows=718258
loops=1)<BR>
-> Hash (cost=2527.53..2527.53 rows=306 width=33) (actual
time=20602.792..20602.792 rows=1462
loops=1)<BR>
-> Merge Join (cost=1493.57..2527.53 rows=306 width=33) (actual
time=19423.825..20596.730 rows=1462
loops=1)<BR>
Merge Cond: (("outer".stop_a)::text =
"inner"."?column5?")<BR>
-> Index Scan using link_stop_a on stop_link d
(cost=0.00..2429.39 rows=49872 width=26) (actual time=0.054..20490.401
rows=20935
l<BR>oops=1)<BR>
-> Sort (cost=1493.57..1493.68 rows=46 width=34) (actual
time=10.635..15.345 rows=1464
loops=1)<BR>
Sort Key:
(e.stop_reference)::text<BR>
-> Nested Loop (cost=8.59..1492.30 rows=46 width=34) (actual
time=0.921..8.670 rows=235
loops=1)<BR>
Join Filter: ("outer".stop_order <
"inner".stop_order)<BR>
-> Nested Loop (cost=8.59..1484.68 rows=1 width=16) (actual
time=0.856..4.633 rows=7
loops=1)<BR>
-> Index Scan using stops_distance1 on bus_stops1 c
(cost=0.00..14.04 rows=1 width=13) (actual time=0.222..0.391<BR> rows=3
loops=1)<BR>
Index Cond: (east_north &&
'0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913<BR>410000000098942441000000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry)<BR>
Filter: ((east_north &&
'0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913410<BR>000000098942441000000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry)
AND
(distance('0101000020346C0000000000002C7013410000<BR>000078912441'::geometry,
east_north) < 200::double
precision))<BR>
-> Bitmap Heap Scan on service1 a (cost=8.59..1464.97 rows=453
width=30) (actual time=0.454..1.384 rows=2
loops=<BR>3)<BR>
Recheck Cond: ((a.stop_reference)::text =
("outer".stop_reference)::text)<BR>
Filter: (((depart_time)::interval >= '13:45:00'::interval) AND
((depart_time)::interval <=
'13:55:00'::inter<BR>val))<BR>
-> Bitmap Index Scan on service1_stop_reference (cost=0.00..8.59
rows=453 width=0) (actual time=0.135..0.1<BR>35 rows=213
loops=3)<BR>
Index Cond: ((a.stop_reference)::text =
("outer".stop_reference)::text)<BR>
-> Index Scan using service1_service_id on service1 e
(cost=0.00..5.71 rows=127 width=30) (actual time=0.022..0.249 ro<BR>ws=62
loops=7)<BR>
Index Cond: (e.service_id =
"outer".service_id)<BR>
-> Index Scan using service1_service_id on service1 i
(cost=0.00..5.71 rows=127 width=30) (actual time=0.755..1.212 rows=61
loops=8093)<BR>
Index Cond: ("outer".service_id =
i.service_id)<BR>
-> Sort (cost=11506.95..11612.00 rows=42022 width=33) (actual
time=15890.055..135847.222 rows=33805165
loops=1)<BR>
Sort Key:
(h.stop_a)::text<BR>
-> Merge Join (cost=5908.77..7568.48 rows=42022 width=33) (actual
time=5842.981..9827.070 rows=209740
loops=1)<BR>
Merge Cond: (("outer".stop_b)::text =
"inner"."?column5?")<BR>
-> Index Scan using link_stop_b on stop_link h
(cost=0.00..2429.52 rows=49872 width=26) (actual time=219.860..4054.111
rows=19252
loops=1)<BR>
-> Sort (cost=5908.77..5924.94 rows=6466 width=34) (actual
time=3098.791..3864.662 rows=210487
loops=1)<BR>
Sort Key:
(j.stop_reference)::text<BR>
-> Nested Loop (cost=8.59..5499.52 rows=6466 width=34) (actual
time=417.778..1919.171 rows=44866
loops=1)<BR>
Join Filter: ("inner".stop_order <
"outer".stop_order)<BR>
-> Nested Loop (cost=8.59..4440.37 rows=139 width=16) (actual
time=386.184..412.717 rows=1907
loops=1)<BR>
-> Index Scan using stops_distance1 on bus_stops1 l
(cost=0.00..42.07 rows=3 width=13) (actual time=302.551..302.750 rows=3
loops=<BR>1)<BR>
Index Cond: (east_north &&
'0103000020346C00000100000005000000000000E0DDEE134100000000147E2441000000E0DDEE13410000002054842441<BR>000000005EFB13410000002054842441000000005EFB134100000000147E2441000000E0DDEE134100000000147E2441'::geometry)<BR>
Filter: ((east_north &&
'0103000020346C00000100000005000000000000E0DDEE134100000000147E2441000000E0DDEE13410000002054842441000<BR>000005EFB13410000002054842441000000005EFB134100000000147E2441000000E0DDEE134100000000147E2441'::geometry)
AND
(distance('0101000020346C0000B5E09BF31DF51341B6E1151534812441'::geo<BR>metry,
east_north) < 200::double
precision))<BR>
-> Bitmap Heap Scan on service1 k (cost=8.59..1460.44 rows=453
width=30) (actual time=28.171..32.343 rows=636
loops=3)<BR>
Recheck Cond: ((k.stop_reference)::text =
("outer".stop_reference)::text)<BR>
-> Bitmap Index Scan on service1_stop_reference (cost=0.00..8.59
rows=453 width=0) (actual time=28.038..28.038 rows=636
loop<BR>s=3)<BR>
Index Cond: ((k.stop_reference)::text =
("outer".stop_reference)::text)<BR>
-> Index Scan using service1_service_id on service1 j
(cost=0.00..5.71 rows=127 width=30) (actual time=0.043..0.516 rows=55
loops=1907)<BR>
Index Cond: (j.service_id = "outer".service_id)<BR> Total runtime:
390871.465 ms</FONT><BR><BR><BR>As you can see it is taking entirely too much
time for a query of this size in my opinion. Therefore im assuming I have done
something wrong or could improve it in some way. I would really appreciate your
help.<BR><BR>Sincerely<BR><BR>Alan<BR><BR><BR><BR></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"
target=_blank rel=nofollow>sign up for your free account
today</A>.</DIV><BR></DIV></DIV><BR>
<HR SIZE=1>
Copy addresses and emails from any email account to Yahoo! Mail - quick, easy
and free. <A
href="http://us.rd.yahoo.com/mail/uk/taglines/yahoo_com/trueswitch/*http://uk.docs.yahoo.com/trueswitch2.html"
target=_blank rel=nofollow>Do it now...</A></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>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>