On Wed, Nov 25, 2015 at 3:12 PM, Stephen Woodbridge <woodbri@swoodbridge.com
wrote:
On 11/25/2015 2:35 PM, Matt Geneau wrote:
Hello,
While looking at ways to speed up my routing queries with pg_trsp, I
noticed my restrictions table is always being hit with sequential scans.
I came across
http://gis.stackexchange.com/questions/109156/pgrouting-what-index-should-have-the-restriction-table
but unfortunately it has no answer.
I tried guessing two indexes but they did not work. While looking
through the trsp.c code on Github to see if that could point me in the
right direction, I was unable to figure out how the restrictions are
being loaded.
Does anyone have any suggestions on the best way to index the
restrictions table?
Hi Matt,
The answer to this really has to do with what you need from the
restrictions table for a given query.
If you always pass the restrictions like: select * geom restrictions; then
you will always do a full table scan and load all the restrictions
regardless of whether or not you need them.
So for example, if you are doing a bbox query on your edges, then it makes
sense to only load the restrictions based on that same bbox. But since the
restrictions tables does not have any geometry you need to fake it with a
join against your edge table.
So if your edges are selected like:
select * from edge_table where geom && bbox;
Then you might consider a restriction query like:
select a.*
from restrictions a,
(select gid from edge_table where geom && bbox) b
where a.to_edge=b.gid;
I'm using gid as the primary key on the edge table and that is also the
values that are in the restrictions table.
In the above example you what indexes on edge_table.gid, edge_table.geom
and it might help to have restrictions.to_edge indexed also for the join.
-Steve
_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users
For some reason I was thinking the restrictions were loaded after the route
is generated, but of course they all need to be loaded beforehand in order
to get a valid route.
Thanks your reply, your suggestions will definitely help.
Thanks,
Matt
In case anyone is curious in the future about filtering which restrictions are loaded:
Instead of doing the barbell bounding boxes directly in the select edge sql passed to pg_trsp, I created a stored procedure that took in the two endpoints and a distance value, which calls ST_DWithin() 3 times to return all edges in a barbell shaped box, based on the distance value. The edge query passed to pg_trsp is then just a select all edges, joined to the SP.
With this I was able to modify the restrictions query passed to pg_trsp to also join the SP results to only load in restrictions that would apply to the same barbell box edges.
In the end I found that it was actually significantly slower to do this join with the restrictions table versus the standard select all restrictions. This was true for restriction tables with 30k and 100k rows.
On the bright side, my barbell bounding box query seems to benefit speed/cache wise from being in a stored procedure since I make several parallel calls to pg_trsp using different cost functions.
Thanks Steve for your help.
Matt
···
On Wed, Nov 25, 2015 at 3:39 PM, Matt Geneau <matt.geneau@gmail.com> wrote:
On Wed, Nov 25, 2015 at 3:12 PM, Stephen Woodbridge <woodbri@swoodbridge.com> wrote:
On 11/25/2015 2:35 PM, Matt Geneau wrote:
Hello,
While looking at ways to speed up my routing queries with pg_trsp, I
noticed my restrictions table is always being hit with sequential scans.
I came across
http://gis.stackexchange.com/questions/109156/pgrouting-what-index-should-have-the-restriction-table
but unfortunately it has no answer.
I tried guessing two indexes but they did not work. While looking
through the trsp.c code on Github to see if that could point me in the
right direction, I was unable to figure out how the restrictions are
being loaded.
Does anyone have any suggestions on the best way to index the
restrictions table?
Hi Matt,
The answer to this really has to do with what you need from the restrictions table for a given query.
If you always pass the restrictions like: select * geom restrictions; then you will always do a full table scan and load all the restrictions regardless of whether or not you need them.
So for example, if you are doing a bbox query on your edges, then it makes sense to only load the restrictions based on that same bbox. But since the restrictions tables does not have any geometry you need to fake it with a join against your edge table.
So if your edges are selected like:
select * from edge_table where geom && bbox;
Then you might consider a restriction query like:
select a.*
from restrictions a,
(select gid from edge_table where geom && bbox) b
where a.to_edge=b.gid;
I’m using gid as the primary key on the edge table and that is also the values that are in the restrictions table.
In the above example you what indexes on edge_table.gid, edge_table.geom and it might help to have restrictions.to_edge indexed also for the join.
-Steve
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users
For some reason I was thinking the restrictions were loaded after the route is generated, but of course they all need to be loaded beforehand in order to get a valid route.
Thanks your reply, your suggestions will definitely help.
Thanks,
Matt