[postgis-users] Massive Performance Issues

[Alan Cunnane]

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 <sfrost at snowman.net>
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Sent: Wednesday, 18 July, 2007 1:17:37 AM
Subject: Re: [postgis-users] Massive Performance Issues

* Alan Cunnane (alan_cunnane at yahoo.co.uk) 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
postgis-users at postgis.refractions.net
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070718/5f0b75bc/attachment.html>