Hi Steven as requested here are the table definitions and constraints:
Table "bus_stops1"
Column | Type | Modifiers
----------------+-----------------------+-----------
stop_reference | character varying(12) | not null
easting | integer | not null
northing | integer | not null
full_location | character varying(50) |
gazetteer_code | character varying(1) |
point_type | character varying(1) |
nat_gazetteer | character varying(7) |
district_name | character varying(24) |
town_name | character varying(24) |
east_north | geometry | not null
Indexes:
"bus_stops1_pkey" PRIMARY KEY, btree (stop_reference)
"stops_distance1" gist (east_north) CLUSTER
Check constraints:
"enforce_dims_east_north" CHECK (ndims(east_north) = 2)
"enforce_geotype_east_north" CHECK (geometrytype(east_north) =
'POINT'::text OR east_north IS NULL)
"enforce_srid_east_north" CHECK (srid(east_north) = 27700)
Table "Routes1"
Column | Type | Modifiers
--------------+----------------------+-----------
service_id | integer | not null
journey_id | integer | not null
start_date | date | not null
end_date | date | not null
route_number | character varying(4) | not null
monday | integer | not null
tuesday | integer | not null
wednesday | integer | not null
thursday | integer | not null
friday | integer | not null
saturday | integer | not null
sunday | integer | not null
schoolterm | character varying(2) |
bankholiday | character varying(2) |
direction | character varying(2) | not null
Indexes:
"routes1_pkey" PRIMARY KEY, btree (service_id)
"routes1_route_number" btree (route_number)
Table "service1"
Column | Type | Modifiers
----------------+------------------------+-----------
service_id | integer | not null
record_id | character varying(2) | not null
stop_reference | character varying(12) | not null
arrival_time | time without time zone |
depart_time | time without time zone |
timing_point | character varying(2) | not null
stop_order | integer | not null
Indexes:
"service1_service_id" btree (service_id)
"service1_stop_order" btree (stop_order)
"service1_stop_reference" btree (stop_reference)
Foreign-key constraints:
"service1_service_id_fkey" FOREIGN KEY (service_id) REFERENCES
routes1(service_id)
"service1_stop_reference_fkey" FOREIGN KEY (stop_reference) REFERENCES
bus_stops1(stop_reference)
Table "stop_link"
Column | Type | Modifiers
--------+-----------------------+-----------
stop_a | character varying(12) |
stop_b | character varying(12) |
Indexes:
"link_stop_a" btree (stop_a)
"link_stop_b" btree (stop_b)
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
----- Original Message ----
From: Stephen Frost <[EMAIL PROTECTED]>
To: PostGIS Users Discussion <[email protected]>
Sent: Wednesday, 18 July, 2007 1:17:37 AM
Subject: Re: [postgis-users] Massive Performance Issues
* Alan Cunnane ([EMAIL PROTECTED]) wrote:
> 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
> FROM service1 a, stop_link b, service1 c, bus_stops1 d, service1 e,
> bus_stops1 f, service1 g, routes1 h, routes1 i
In general I find that it's much clearer to use JOIN syntax rather than
doing a cartesian join and then limiting it using where clauses. It can
also help identify joins which aren't being properly constrained.
> WHERE distance(PointFromText('POINT(318475 673980)', 27700),f.east_north) <
> 200
> AND distance(PointFromText('POINT(331727 664569)', 27700),d.east_north) < 200
You should include a bounding box restriction before doing distance
calculations to limit the amount of data being run through the distance
function, ie:
WHERE f.east_north &&
setsrid(box3d(expand(PointFromText('POINT(318475
673980)',27700),400)),27700)
AND
distance(PointFromText('POINT(318475 673980)', 27700),f.east_north) < 200
AND
d.east_north &&
setsrid(box3d(expand(PointFromText('POINT(331727
664569)',27700),400)),27700)
AND
distance(PointFromText('POINT(331727 664569)', 27700),d.east_north) < 200
> 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:
Seeing the rest of it would certainly help, along with your table
definitions and foreign-key constraints and indexes...
Thanks,
Stephen
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
___________________________________________________________
All New Yahoo! Mail – Tired of unwanted email come-ons? Let our SpamGuard
protect you. http://uk.docs.yahoo.com/nowyoucan.html_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
