[pgrouting-users] pg_trsp Restrctions Table Index

[Stephen Woodbridge]

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