3

I have this tables to feed my routing engine using pgr_trsp:
vzla_rto have the links and nodes (650k)
vzla_restriction have the turn restrictions (10k)

CREATE TABLE map.vzla_restrictions
(
  rid integer NOT NULL,
  to_cost numeric,
  to_edge integer,
  from_edge integer,
  via text
);
CREATE INDEX vzla_restrictions_from_edge_idx ON map.vzla_restrictions USING btree (from_edge);
CREATE INDEX vzla_restrictions_to_edge_idx ON map.vzla_restrictions USING btree (to_edge);

I was checking table statistics and realize i put an effort to create index for vzla_rto, but looks like i didn't enough for vzla_restrictions.

                    | seq_scan      | seq_tu_read     | idx_scan        | idx_tup_fetch | n_tup_ins
vzla_restrictions   | 1,672,959     | 16,848,370,089  |      -          | -             | 10071
vzla_rto            | 1,549         | 977,994,776     |  862,610,399    | 3,866,967,927 | 648968

So which index should i add to vzla_restrictions to help pgr_trsp()?

1 Answer 1

3

The answer to this really has to do with what you need from the restrictions table for a given query.

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.

4
  • That only reduce the number of restrictions. But the real optimization should be trsp use the proper index to find the gid involve. Commented Nov 25, 2015 at 20:13
  • trsp reads all the restrictions and edges into C++ structures and then it computes the solution. Once the data is read in we do not use the database until we need to return the results. I don't understand what you are asking for. Also we are going to be making some changes in trsp for pgrouting 2.2 so you might want to open a ticket in github and explain your request better. Commented Nov 27, 2015 at 0:02
  • I understand now because you say tsrp read all and not use db for the calculations. I already do the box thing for the road table before pass to tsrp function. So my guess all the index are from that previous step and not the tsrp function itself. Commented Nov 30, 2015 at 14:45
  • 1
    Yes this is correct. We can only make use of the indexes when executing the sql query for fetching the edges on the restriction. Once they are fetched, we don't use the database until we are ready to return the results. Commented Dec 1, 2015 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.