Start by using Explain SELECT ...
And compare to see if the query planner is running the same way.
Thanks,
Alex
On 05/12/2015 02:33 AM, McDonaldR wrote:
> Hello list
>
> I have a question about how QGIS (specifically the pgRouting Layer plugin)
> uses psycopg2 and a connection to PostgreSQL to run a query and return a
> result.
>
> I am using pgRouting with Ordnance Survey's Open Roads network for the UK.
> It is a large network with over 3 million links and 2.6 million nodes.
> Using the pgRouting Layer plugin in QGIS 2.6.1 and 2.8.2 to interact with the
> network. It is really slow to solve routes ~ 1m20s per solution. I am also
> using PgAdmin3 to run the same queries against the same network and they are
> also slow (~1m20s) but I can speed them up using a bounding box in the query
> (in some cases by 400x!). In the pgRouting Layer plugin I have updated the
> djikstra.py file to use the query with a bounding box but the result is still
> returned in ~ 1m20s. Running the same query in the SQL pane in DB Manager
> returns the result in ~ 100ms.
>
> So, I guess the question is "Is psycopg2 (or python) the bottleneck in
> running queries in the pgRouting Layer plugin?" and maybe a secondary
> question is "how does DB Manager connect to the database and run the query so
> quickly?"
>
> The queries
>
> Without bounding box ~ 1m20s
> SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
> SELECT gid AS id,
> source::integer,
> target::integer,
> cost_len::double precision AS cost,
> rcost_len::double precision AS reverse_cost
> FROM or_network',
> 1190869, 586365, false, true);
>
> With a bounding box ~ 200ms
> SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
> SELECT gid AS id,
> source::integer,
> target::integer,
> cost_len::double precision AS cost,
> rcost_len::double precision AS reverse_cost
> FROM or_network
> WHERE geometry && ST_Expand(
> (SELECT ST_Collect(the_geom) FROM
> or_network_vertices_pgr WHERE id IN (1190869, 586365)),2000)',
> 1190869, 586365, false, true);
>
> Thanks in advance
>
> Ross
>
>
> Ross McDonald | GIS Data Coordinator | Resources Department, IT Division |
> Angus Council, Angus House, Orchardbank Business Park, Forfar, DD8 1AT
> T: 01307 476419 | F: 01307 476401 | E:
> [email protected]<mailto:[email protected]>
>
>
>
> This message is strictly confidential. If you have received this in error,
> please inform the sender and remove it from your system. If received in error
> you may not copy, print, forward or use it or any attachment in any way. This
> message is not capable of creating a legal contract or a binding
> representation and does not represent the views of Angus Council. Emails may
> be monitored for security and network management reasons. Messages containing
> inappropriate content may be intercepted. Angus Council does not accept any
> liability for any harm that may be caused to the recipient system or data on
> it by this message or any attachment.
>
>
>
> _______________________________________________
> Qgis-developer mailing list
> [email protected]
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>
_______________________________________________
Qgis-developer mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-developer
