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?
Thanks,
Matt
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