[Qgis-developer] Query performance in QGIS using psycopg2

[Alex Mandel]

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: mcdonaldr at angus.gov.uk<mailto:mcdonaldr at angus.gov.uk>
> 
> 
> 
> 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
> Qgis-developer at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>