<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;">Hi Steven as requested here are the table definitions and constraints:<br><br> Table "bus_stops1"<br> Column | Type | Modifiers<br>----------------+-----------------------+-----------<br> stop_reference | character varying(12) | not null<br> easting | integer | not null<br> northing |
integer | not null<br> full_location | character varying(50) |<br> gazetteer_code | character varying(1) |<br> point_type | character varying(1) |<br> nat_gazetteer | character varying(7) |<br> district_name | character varying(24) |<br> town_name | character varying(24) |<br> east_north | geometry | not null<br>Indexes:<br> "bus_stops1_pkey" PRIMARY KEY, btree (stop_reference)<br> "stops_distance1" gist (east_north) CLUSTER<br>Check constraints:<br> "enforce_dims_east_north" CHECK (ndims(east_north) = 2)<br> "enforce_geotype_east_north" CHECK (geometrytype(east_north) = 'POINT'::text OR
east_north IS NULL)<br> "enforce_srid_east_north" CHECK (srid(east_north) = 27700)<br><br><br><br> Table "Routes1"<br> Column | Type | Modifiers<br>--------------+----------------------+-----------<br> service_id | integer | not null<br> journey_id | integer | not null<br> start_date | date | not null<br> end_date | date | not null<br> route_number
| character varying(4) | not null<br> monday | integer | not null<br> tuesday | integer | not null<br> wednesday | integer | not null<br> thursday | integer | not null<br> friday | integer | not null<br> saturday | integer | not null<br> sunday |
integer | not null<br> schoolterm | character varying(2) |<br> bankholiday | character varying(2) |<br> direction | character varying(2) | not null<br>Indexes:<br> "routes1_pkey" PRIMARY KEY, btree (service_id)<br> "routes1_route_number" btree (route_number)<br><br><br><br> Table "service1"<br> Column | Type | Modifiers<br>----------------+------------------------+-----------<br> service_id | integer | not null<br> record_id | character
varying(2) | not null<br> stop_reference | character varying(12) | not null<br> arrival_time | time without time zone |<br> depart_time | time without time zone |<br> timing_point | character varying(2) | not null<br> stop_order | integer | not null<br>Indexes:<br> "service1_service_id" btree (service_id)<br> "service1_stop_order" btree (stop_order)<br> "service1_stop_reference" btree (stop_reference)<br>Foreign-key constraints:<br> "service1_service_id_fkey" FOREIGN KEY (service_id) REFERENCES routes1(service_id)<br> "service1_stop_reference_fkey" FOREIGN KEY (stop_reference) REFERENCES bus_stops1(stop_reference)<br><br><br><br>
Table "stop_link"<br> Column | Type | Modifiers<br>--------+-----------------------+-----------<br> stop_a | character varying(12) |<br> stop_b | character varying(12) |<br>Indexes:<br> "link_stop_a" btree (stop_a)<br> "link_stop_b" btree (stop_b)<br><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><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;">----- 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:17:37 AM<br>Subject: Re: [postgis-users] Massive Performance Issues<br><br><div>*
Alan Cunnane (alan_cunnane@yahoo.co.uk) wrote:<br>> SELECT f.stop_reference AS origstop, a.stop_reference AS origconnect, h.route_number, c.stop_reference AS destconnect, i.route_number, d.stop_reference AS deststop, (e.arrival_time - g.depart_time) AS time <br>> FROM service1 a, stop_link b, service1 c, bus_stops1 d, service1 e, bus_stops1 f, service1 g, routes1 h, routes1 i <br><br>In general I find that it's much clearer to use JOIN syntax rather than<br>doing a cartesian join and then limiting it using where clauses. It can<br>also help identify joins which aren't being properly constrained.<br><br>> WHERE distance(PointFromText('POINT(318475 673980)', 27700),f.east_north) < 200 <br>> AND distance(PointFromText('POINT(331727 664569)', 27700),d.east_north) < 200 <br><br>You should include a bounding box restriction before doing distance<br>calculations to limit the amount of data being run through the distance<br>function, ie:<br><br>WHERE
f.east_north && <br> setsrid(box3d(expand(PointFromText('POINT(318475 673980)',27700),400)),27700)<br> AND<br> distance(PointFromText('POINT(318475 673980)', 27700),f.east_north) < 200<br> AND<br> d.east_north &&<br> setsrid(box3d(expand(PointFromText('POINT(331727 664569)',27700),400)),27700)<br> AND<br> distance(PointFromText('POINT(331727 664569)', 27700),d.east_north) < 200<br><br>> I have limited the query to just one result and have indexes built on all the columns that are being linked however this has not helped the performance at all. In fact when I run an EXPLAIN the cost is massive! See below:<br><br>Seeing the rest of it would certainly help, along with your
table<br>definitions and foreign-key constraints and indexes...<br><br> Thanks,<br><br> Stephen<br></div><div>_______________________________________________<br>postgis-users mailing list<br>postgis-users@postgis.refractions.net<br><a target="_blank" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></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>