[pgrouting-users] pg_trsp Restrctions Table Index

[Matt Geneau]

On Wed, Nov 25, 2015 at 3:12 PM, Stephen Woodbridge <woodbri at 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 at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20151125/0e1a1382/attachment.html>