[postgis-users] Fw: Performance Help
Obe, Regina
robe.dnd at cityofboston.gov
Fri Jul 27 07:42:02 PDT 2007
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.
So you can change
l.east_north &&
setsrid(box3d(expand(PointFromText('POINT(326983.487899314
671898.041182569)',27700),400)),27700)
to
l.east_north && expand(PointFromText('POINT(326983.487899314
671898.041182569)',27700),400)
Also makepoint is a bit faster than pointfromtext, but since you are
dealing with static points, I guess that is not terribly important.
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
You should just be doing
l.east_north && expand(PointFromText('POINT(326983.487899314
671898.041182569)',27700),200)
Hope that helps,
Regina
________________________________
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Alan
Cunnane
Sent: Wednesday, July 25, 2007 10:10 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Fw: Performance Help
Hi guys
I have 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 change 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:
SELECT a.service_id, f.service_id, j.service_id, (k.arrival_time -
a.depart_time) AS time
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
WHERE c.east_north &&
setsrid(box3d(expand(PointFromText('POINT(318475
673980)',27700),400)),27700)
AND l.east_north &&
setsrid(box3d(expand(PointFromText('POINT(326983.487899314
671898.041182569)',27700),400)),27700)
AND distance(PointFromText('POINT(326983.487899314 671898.041182569)',
27700),l.east_north) < 200
AND distance(PointFromText('POINT(318475 673980)', 27700),c.east_north)
< 200
AND a.depart_time BETWEEN '13:50' - interval '5minutes' AND '13:50' +
interval '5 minutes'
AND f.depart_time BETWEEN e.depart_time + interval '3minutes' AND
e.depart_time + interval '13 minutes'
AND j.depart_time BETWEEN i.depart_time + interval '3minutes' AND
i.depart_time + interval '13 minutes'
AND a.stop_reference = c.stop_reference
AND e.service_id = a.service_id
AND d.stop_a = e.stop_reference
AND d.stop_b = f.stop_reference
AND f.service_id = i.service_id
AND h.stop_a = i.stop_reference
AND h.stop_b = j.stop_reference
AND j.service_id = k.service_id
AND k.stop_reference = l.stop_reference
AND a.stop_order < e.stop_order
AND f.stop_order < i.stop_order
AND j.stop_order < k.stop_order
ORDER BY time, a.depart_time
LIMIT 1;
This is the EXPLAIN ANALYZE of this query:
Limit (cost=103086.88..103086.88 rows=1 width=28) (actual
time=390489.993..390489.997 rows=1 loops=1)
-> Sort (cost=103086.88..103316.50 rows=91849 width=28) (actual
time=390489.985..390489.985 rows=1 loops=1)
Sort Key: (k.arrival_time - a.depart_time), a.depart_time
-> Merge Join (cost=74721.84..93887.80 rows=91849 width=28)
(actual time=74200.285..380077.222 rows=417915 loops=1)
Merge Cond: ("outer"."?column6?" = "inner"."?column5?")
Join Filter: (("inner".depart_time >=
("outer".depart_time + '00:03:00'::interval)) AND ("inner".depart_time
<= ("outer".depart_time + '00:13:00'::interval)))
-> Sort (cost=63214.89..63583.86 rows=147585 width=38)
(actual time=58256.350..59157.265 rows=233571 loops=1)
Sort Key: (i.stop_reference)::text
-> Nested Loop (cost=2528.29..45913.84
rows=147585 width=38) (actual time=20839.047..50211.661 rows=242984
loops=1)
Join Filter: ("outer".stop_order <
"inner".stop_order)
-> Hash Join (cost=2528.29..21682.94
rows=3180 width=20) (actual time=20838.982..37804.115 rows=8093 loops=1)
Hash Cond:
(("outer".stop_reference)::text = ("inner".stop_b)::text)
Join Filter: (("outer".depart_time >=
("inner".depart_time + '00:03:00'::interval)) AND ("outer".depart_time
<= ("inner".depart_time + '00:13:00
'::interval)))
-> Seq Scan on service1 f
(cost=0.00..14990.58 rows=718258 width=30) (actual time=0.043..14325.330
rows=718258 loops=1)
-> Hash (cost=2527.53..2527.53
rows=306 width=33) (actual time=20602.792..20602.792 rows=1462 loops=1)
-> Merge Join
(cost=1493.57..2527.53 rows=306 width=33) (actual
time=19423.825..20596.730 rows=1462 loops=1)
Merge Cond:
(("outer".stop_a)::text = "inner"."?column5?")
-> 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
oops=1)
-> Sort
(cost=1493.57..1493.68 rows=46 width=34) (actual time=10.635..15.345
rows=1464 loops=1)
Sort Key:
(e.stop_reference)::text
-> Nested Loop
(cost=8.59..1492.30 rows=46 width=34) (actual time=0.921..8.670 rows=235
loops=1)
Join Filter:
("outer".stop_order < "inner".stop_order)
-> Nested Loop
(cost=8.59..1484.68 rows=1 width=16) (actual time=0.856..4.633 rows=7
loops=1)
-> Index
Scan using stops_distance1 on bus_stops1 c (cost=0.00..14.04 rows=1
width=13) (actual time=0.222..0.391
rows=3 loops=1)
Index Cond: (east_north &&
'0103000020346C0000010000000500000000000000EC69134100000000588E244100000
000EC6913
410000000098942441000000006C7613410000000098942441000000006C761341000000
00588E244100000000EC69134100000000588E2441'::geometry)
Filter: ((east_north &&
'0103000020346C0000010000000500000000000000EC69134100000000588E244100000
000EC6913410
000000098942441000000006C7613410000000098942441000000006C761341000000005
88E244100000000EC69134100000000588E2441'::geometry) AND
(distance('0101000020346C0000000000002C7013410000
000078912441'::geometry, east_north) < 200::double precision))
->
Bitmap Heap Scan on service1 a (cost=8.59..1464.97 rows=453 width=30)
(actual time=0.454..1.384 rows=2 loops=
3)
Recheck Cond: ((a.stop_reference)::text =
("outer".stop_reference)::text)
Filter: (((depart_time)::interval >= '13:45:00'::interval) AND
((depart_time)::interval <= '13:55:00'::inter
val))
->
Bitmap Index Scan on service1_stop_reference (cost=0.00..8.59 rows=453
width=0) (actual time=0.135..0.1
35 rows=213 loops=3)
Index Cond: ((a.stop_reference)::text = ("outer".stop_reference)::text)
-> 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
ws=62 loops=7)
Index
Cond: (e.service_id = "outer".service_id)
-> 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)
Index Cond: ("outer".service_id =
i.service_id)
-> Sort (cost=11506.95..11612.00 rows=42022 width=33)
(actual time=15890.055..135847.222 rows=33805165 loops=1)
Sort Key: (h.stop_a)::text
-> Merge Join (cost=5908.77..7568.48 rows=42022
width=33) (actual time=5842.981..9827.070 rows=209740 loops=1)
Merge Cond: (("outer".stop_b)::text =
"inner"."?column5?")
-> 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)
-> Sort (cost=5908.77..5924.94 rows=6466
width=34) (actual time=3098.791..3864.662 rows=210487 loops=1)
Sort Key: (j.stop_reference)::text
-> Nested Loop (cost=8.59..5499.52
rows=6466 width=34) (actual time=417.778..1919.171 rows=44866 loops=1)
Join Filter: ("inner".stop_order
< "outer".stop_order)
-> Nested Loop
(cost=8.59..4440.37 rows=139 width=16) (actual time=386.184..412.717
rows=1907 loops=1)
-> 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=
1)
Index Cond:
(east_north &&
'0103000020346C00000100000005000000000000E0DDEE134100000000147E244100000
0E0DDEE13410000002054842441
000000005EFB13410000002054842441000000005EFB134100000000147E2441000000E0
DDEE134100000000147E2441'::geometry)
Filter: ((east_north
&&
'0103000020346C00000100000005000000000000E0DDEE134100000000147E244100000
0E0DDEE13410000002054842441000
000005EFB13410000002054842441000000005EFB134100000000147E2441000000E0DDE
E134100000000147E2441'::geometry) AND
(distance('0101000020346C0000B5E09BF31DF51341B6E1151534812441'::geo
metry, east_north) < 200::double precision))
-> 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)
Recheck Cond:
((k.stop_reference)::text = ("outer".stop_reference)::text)
-> 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
s=3)
Index Cond:
((k.stop_reference)::text = ("outer".stop_reference)::text)
-> 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)
Index Cond: (j.service_id =
"outer".service_id)
Total runtime: 390871.465 ms
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.
Sincerely
Alan
________________________________
Yahoo! Mail is the world's favourite email. Don't settle for less, sign
up for your free account today
<http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter0
7.html> .
________________________________
Copy addresses and emails from any email account to Yahoo! Mail - quick,
easy and free. Do it now...
<http://us.rd.yahoo.com/mail/uk/taglines/yahoo_com/trueswitch/*http://uk
..docs.yahoo.com/trueswitch2.html>
________________________________
Yahoo! Mail is the world's favourite email. Don't settle for less, sign
up for your free account today
<http://uk.rd.yahoo.com/evt=44106/*http://uk.docs.yahoo.com/mail/winter0
7.html> .
-----------------------------------------
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070727/c0a39cf6/attachment.html>
More information about the postgis-users
mailing list