[pgrouting-users] Performance problems with pgr_dijkstra

[Eric Scheibler]

Eric Scheibler <email at eric-scheibler.de> schrieb am 11.02.2015, 12:23 +0100:
>Daniel Kastl <daniel at georepublic.de> schrieb am 11.02.2015, 10:54 +0900:
>>You're right, that with pgRouting the amount of data selected from the
>>network table matters. And the fastest way to select only a part of the
>>network table is by selecting a bounding box. You should have an index on
>>your geometry column as well. Then you don't need to create temporary
>>tables.
>
>Do you have an example for a bounding box? How to determine the box size? I know the distance
>between the starting and destination point in meters. Could that be used?

Found that, works.

>>Back to your question: as far as I remember, the size of ID's can matter. I
>>experienced this when I used data, that had already source and target ID's
>>in place, which all had the same number of digits. Renumbering (starting
>>from 1) helped to improve the speed. Though I can't tell this is the reason
>>in your case.
>
>Very interesting. You could be right. I created a temp routing table in the Saxony database, took
>start and destination vertex from my program and verified the process time and the result (4 rows
>and 60 ms for a very short way, approximately 100 meters). Then I dumped the created table with
>pg_dump and restored it into the Europe database. Now the same routing query runs as fast as in the
>small database. So maybe the higher source and target id's are responsible for that.

Yes, that solved the problem. Now the routing query completes after 30-40 ms. So it's even a bit
faster than at the small database. I've created a SQL function, which recreates the source and
target id's of the temp routing table:

CREATE OR REPLACE FUNCTION recreate_vertex_of_routing_table(regclass)
RETURNS void
AS $$
DECLARE
    row RECORD;
    vertex_storage hstore;
    new_vertex int;
BEGIN
    vertex_storage := ''::hstore;
    new_vertex := 1;
    FOR row in EXECUTE FORMAT('SELECT id, source, target FROM %I', $1)
    LOOP
        IF NOT vertex_storage ? row.source::text THEN
            vertex_storage = vertex_storage || (row.source::text => new_vertex::text);
            new_vertex := new_vertex + 1;
        END IF;
        IF NOT vertex_storage ? row.target::text THEN
            vertex_storage = vertex_storage || (row.target::text => new_vertex::text);
            new_vertex := new_vertex + 1;
        END IF;
    END LOOP;
    FOR row IN SELECT key, value FROM EACH(vertex_storage)
    LOOP
        EXECUTE FORMAT('UPDATE %I SET source=$1 WHERE source = $2', $1) USING row.value::int, row.key::int;
        EXECUTE FORMAT('UPDATE %I SET target=$1 WHERE target = $2', $1) USING row.value::int, row.key::int;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Best regards
Eric
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: Digital signature
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150211/d33fef4d/attachment.pgp>