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.
Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company
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.
(My corporate masters made me say this.)
-----Original Message-----
From: [EMAIL PROTECTED] on behalf of Alan Cunnane
Sent: Tue 7/17/2007 7:26 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Massive Performance Issues
Ah I am sorry here is the eplain analyze requested. The explain analyze query
is shown below:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
Limit (cost=150061918.96..150061918.97 rows=1 width=166) (actual
time=71135.765..71135.765 rows=0 loops=1)
-> Sort (cost=150061918.96..150407602.50 rows=138273415 width=166) (actual
time=71135.758..71135.758 rows=0 loops=1)
Sort Key: (e.arrival_time - g.depart_time)
-> Merge Join (cost=665925.37..32125314.72 rows=138273415 width=166)
(actual time=71135.734..71135.734 rows=0 loops=1)
Merge Cond: ("outer"."?column8?" = "inner"."?column7?")
Join Filter: (("outer".depart_time >= ("inner".depart_time +
'00:03:00'::interval)) AND ("outer".depart_time <= ("inner".depart_time +
'00:13:00'::interval)))
-> Sort (cost=441268.22..442374.55 rows=442532 width=115)
(actual time=70772.175..70772.175 rows=1 loops=1)
Sort Key: (c.stop_reference)::text
-> Hash Join (cost=49458.47..324978.85 rows=442532
width=115) (actual time=23959.230..55506.743 rows=194484 loops=1)
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) (actual time=20936.503..47681.549 rows=1436516 loops=1)
-> Hash (cost=49458.01..49458.01 rows=185
width=77) (actual time=1250.002..1250.002 rows=3168 loops=1)
-> Nested Loop (cost=54.14..49458.01
rows=185 width=77) (actual time=352.784..1231.899 rows=3168 loops=1)
Join Filter:
(("outer".route_number)::text <> ("inner".route_number)::text)
-> 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)
Index Cond: (6867 = service_id)
-> Nested Loop (cost=54.14..49449.68
rows=186 width=53) (actual time=305.686..1160.476 rows=3168 loops=1)
-> Nested Loop
(cost=54.14..48330.43 rows=186 width=29) (actual time=88.261..798.839 rows=3168
loops=1)
-> 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
ops=1)
Index Cond:
(east_north &&
'0103000020346C0000010000000500000000000000D42E1441000000001C82244100000000D42E1441000000005C
88244100000000543B1441000000005C88244100000000543B1441000000001C82244100000000D42E1441000000001C822441'::geometry)
Filter: ((east_north
&&
'0103000020346C0000010000000500000000000000D42E1441000000001C82244100000000D42E1441000000005C882
44100000000543B1441000000005C88244100000000543B1441000000001C82244100000000D42E1441000000001C822441'::geometry)
AND (distance('0101000020346C00000000000014351441000000003C852441
'::geometry, east_north) < 200::double precision))
-> 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)
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) (actual
time=16.058..16.058 rows=
1056 loops=3)
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) (actual time=0.095..0.100
rows=1 loops=3168)
Index Cond: (i.service_id =
"outer".service_id)
-> Sort (cost=224657.14..226063.21 rows=562427 width=117)
(actual time=363.545..363.545 rows=0 loops=1)
Sort Key: (b.stop_b)::text
-> Merge Join (cost=12136.48..23011.69 rows=562427
width=117) (actual time=363.523..363.523 rows=0 loops=1)
Merge Cond: (("outer".stop_a)::text =
"inner"."?column6?")
-> 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)
-> Sort (cost=12136.48..12142.04 rows=2224
width=75) (actual time=271.807..271.822 rows=4 loops=1)
Sort Key: (a.stop_reference)::text
-> Nested Loop (cost=2864.85..12012.84
rows=2224 width=75) (actual time=242.716..271.730 rows=4 loops=1)
-> Nested Loop (cost=96.53..501.68
rows=1 width=29) (actual time=147.867..147.927 rows=2 loops=1)
-> 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)
Index Cond: (east_north &&
'0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913410000000098942441
000000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry)
Filter: ((east_north &&
'0103000020346C0000010000000500000000000000EC69134100000000588E244100000000EC6913410000000098942441000
000006C7613410000000098942441000000006C76134100000000588E244100000000EC69134100000000588E2441'::geometry)
AND (distance('0101000020346C0000000000002C7013410000000078912441'::geo
metry, east_north) < 200::double precision))
-> 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)
Recheck Cond: ((6867 =
g.service_id) AND (("outer".stop_reference)::text = (g.stop_reference)::text))
-> BitmapAnd
(cost=96.53..96.53 rows=36 width=0) (actual time=37.766..37.766 rows=0 loops=3)
-> Bitmap Index Scan
on service1_service_id (cost=0.00..42.14 rows=7183 width=0) (actual
time=21.129..21.129 rows=130
loops=3)
Index Cond:
(6867 = service_id)
-> Bitmap Index Scan
on service1_stop_reference (cost=0.00..54.14 rows=7183 width=0) (actual
time=16.621..16.621 rows=
426 loops=3)
Index Cond:
(("outer".stop_reference)::text = (g.stop_reference)::text)
-> 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)
Recheck Cond: ((a.service_id =
6867) AND ("outer".stop_order < a.stop_order))
-> BitmapAnd
(cost=2768.32..2768.32 rows=2394 width=0) (actual time=61.828..61.828 rows=0
loops=2)
-> 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)
Index Cond:
(service_id = 6867)
-> Bitmap Index Scan on
service1_stop_order (cost=0.00..2725.94 rows=478839 width=0) (actual
time=61.669..61.669 rows=79104
loops=2)
Index Cond:
("outer".stop_order < a.stop_order)
Total runtime: 71155.967 ms
(55 rows)
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:
CREATE table stops_link (stop_a, stop_b)
AS SELECT DISTINCT a.stop_reference, b.stop_reference
FROM bus_stops1 a, bus_stops1 b, service1 c, service1 d
WHERE distance(a.east_north, b.east_north) < 200
AND expand(a.east_north, 200) && b.east_north
AND a.stop_reference = c.stop_reference
AND b.stop_reference = d.stop_reference
AND c.service_id != d.service_id
AND a.stop_reference != b.stop_reference;
Sorry if it seems im bombarding you with questions now its just you have been
so helpful so far!
----- Original Message ----
From: Stephen Frost <[EMAIL PROTECTED]>
To: PostGIS Users Discussion <[email protected]>
Sent: Wednesday, 18 July, 2007 2:04:17 AM
Subject: Re: [postgis-users] Massive Performance Issues
* Alan Cunnane ([EMAIL PROTECTED]) wrote:
> Already the bounding box seems to be making a big difference as you can see.
> This is th eentire EXPLAIN output:
[...]
> 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
Can you run the query with an 'explain analyze' so we can see where the
time is actually being spent?
Is the query generating the correct result?
Thanks,
Stephen
> ----- 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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
___________________________________________________________
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/winter07.html
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
