Hi Vicky,
The new signature is working now.
The tests are quite impressive. Here I summarize the results, while the test queries are given below. Two tests have been done: speed/scalability, and correctness. The speed/scalability test shows that the new signature scales linearly, and can afford a big number of (source, target) combinations. It was possible to test up to 1e5 combinations. The 1e6 test ran out of memory. I think that the reason is that the size of the result is too big for the memory. The correctness test shows that the result is exactly the same as what one would get using multiple calls to pgr_dijkstra(edges, source, target).
In another email, I’ll share the git repository with you. Please guide us in the following:
- what kind of testing shall be needed, and how to integrate it with the repository.
- how to manage our fork, to ease the merge with pgRouting. The steps done so far: fork → branch ‘combinationsSQL’ → multiple push → merge into master
- what kind of documentation shall be needed, and how to integrate it with the repository. In the code, I copied the coding and commenting styles as good as I could observe.
best regards,
Mahmoud
--------------------Speed/scalability test:
do $$
declare
i int;
count bigint;
startTime timestamptz;
endTime timestamptz;
begin
for i in 0…100000 BY 10000 loop
DROP TABLE IF EXISTS temp;
CREATE TABLE temp AS (
select s.id as source, t.id as target
from nodes s, nodes t
limit i
);
SELECT clock_timestamp() INTO startTime;
→ begin
PERFORM count(*)
FROM pgr_dijkstra(
‘SELECT id, source, target, cost_s AS cost, reverse_cost_s as reverse_cost FROM edges’,
‘SELECT source, target FROM temp’, true);
→ end
SELECT clock_timestamp() INTO endTime;
raise notice ‘count = %, time = %’, i, endTime - startTime;
end loop;
end; $$
NOTICE: count = 0, time = 00:00:00.127069
NOTICE: count = 10000, time = 00:00:04.514361
NOTICE: count = 20000, time = 00:00:08.476031
NOTICE: count = 30000, time = 00:00:11.895231
NOTICE: count = 40000, time = 00:00:16.177429
NOTICE: count = 50000, time = 00:00:19.482188
NOTICE: count = 60000, time = 00:00:24.499867
NOTICE: count = 70000, time = 00:00:34.240081
NOTICE: count = 80000, time = 00:00:35.538692
NOTICE: count = 90000, time = 00:00:44.128275
NOTICE: count = 100000, time = 00:00:48.345672
--------------------Correctness test:
WITH solution1 AS(
SELECT *
FROM pgr_dijkstra(
‘SELECT id, source, target, cost_s AS cost, reverse_cost_s as reverse_cost FROM edges’,
‘SELECT homeNode as source, workNode as target FROM Vehicle’,
true)
)
, solution2 AS(
SELECT * FROM
(SELECT * FROM Vehicle) V,
pgr_dijkstra(
‘SELECT id, source, target, cost_s AS cost, reverse_cost_s as reverse_cost FROM edges’,
V.homeNode, V.workNode, true) P
)
SELECT count() AS Joined,
(SELECT count() FROM solution1) AS NewSolution,
(SELECT count(*) FROM solution2) AS CurrentSolution
FROM solution1 a, solution2 b
WHERE a.start_vid = b.homeNode AND a.end_vid = b.workNode AND a.path_seq = b.path_seq
–Successfully run. Total query runtime: 2 min 2 secs.
–21121; 21121; 21121
Best regards,
Mahmoud Sakr
https://drive.google.com/drive/u/0/folders/1uUWb-AtJGnckBDMBuFT_84ywksjoPALB
···
Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44,
81739 München, Germany
Vicky Vergara
Operations Research
eMail: vicky@[georepublic.de](http://georepublic.de)
Web: [https://georepublic.info](https://georepublic.info)
Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9
Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl
