OpenCypher: PROFILE doesn't give much information

[ExtReMLapin]

Hello,

Ref :

Literally nothing lmao

OpenCypher Query Profile
========================

Execution Time: 7040,277 ms
Rows Returned: 285000

Execution Plan (Traditional):
Step-by-step interpretation

Originally posted by @ExtReMLapin in #3405

[lvca]

What was the full query in this case?

[lvca]

Ok, that was an easy fix, please @ExtReMLapin retry it.

[ExtReMLapin]

I have to admit I'm a little confused by the output :

OpenCypher Query Profile
========================

Execution Time: 6879,412 ms
Rows Returned: 280500

Execution Plan (Traditional):
+ DUMMY ROW (for standalone expressions)
+ WITH 500 AS nb_persons, 500 AS min_pings, 500 AS max_pings, datetime('2024-01-01T00:00:00') AS start_date, 7 * 24 * 60 * 60 AS time_window_sec, 48.80 AS lat_min, 48.90 AS lat_max, 2.25 AS lon_min, 2.40 AS lon_max ()
+ UNWIND range(1, nb_persons) AS i ()
+ WITH i, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max, rand() AS r_dev_distro ()
+ WITH i, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max, CASEWHENr_dev_distro<0.90THEN1WHENr_dev_distro<0.97THEN2ELSE3END AS num_devices ()
+ CREATE ()
+ WITH p, num_devices, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max ()
+ UNWIND range(1, num_devices) AS d_idx ()
+ CREATE ()
+ CREATE ()
+ WITH d, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max, toInteger(rand() * max_pings - min_pings + min_pings) AS nb_pings_target ()
+ UNWIND range(1, nb_pings_target) AS p_idx ()
+ WITH d, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max, rand() AS r_lat, rand() AS r_lon, rand() AS r_time ()
+ CREATE ()
+ CREATE ()

CASEWHENr_dev_distro<0.90THEN1WHENr_dev_distro<0.97THEN2ELSE3END missing spaces ?

And in records (limit auto at 25):

       "records": [
            {
                "d": {
                    "@rid": "#4:6769",
                    "@type": "Device",
                    "@cat": "v",
                    "imei": "IMEI_0.19013481159882295_1",
                    "num": "0654361807",
                    "@out": 500,
                    "@in": 1
                },
                "start_date": "2024-01-01T00:00Z",
                "time_window_sec": 604800,
                "lat_min": 48.8,
                "lat_max": 48.9,
                "lon_min": 2.25,
                "lon_max": 2.4,
                "r_lat": 0.9681435839317251,
                "r_lon": 0.522629433513695,
                "r_time": 0.26180353673079526,
                "ping": {
                    "@rid": "#8:4458697",
                    "@type": "Ping",
                    "@cat": "v",
                    "location": "Pt(x=48.89681435839317,y=2.3283944150270544)",
                    "time": "2024-01-02T19:58:58Z",
                    "@out": 0,
                    "@in": 1
                }
            },

For reference, query is :

 PROFILE  WITH 
    
    500   AS nb_persons,          
    500  AS min_pings,           
    500 AS max_pings,           
    datetime('2024-01-01T00:00:00') AS start_date, 
    7 * 24 * 60 * 60 AS time_window_sec, 
    
    
    48.80 AS lat_min, 48.90 AS lat_max,
    2.25 AS lon_min, 2.40 AS lon_max
    


UNWIND range(1, nb_persons) AS i
WITH i, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max,
     rand() AS r_dev_distro


WITH i, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max,
     CASE 
        WHEN r_dev_distro < 0.90 THEN 1 
        WHEN r_dev_distro < 0.97 THEN 2 
        ELSE 3 
     END AS num_devices

CREATE (p:Person {
    name: "Suspect_" + tostring(rand()), 
    dataset_batch: start_date        
})


WITH p, num_devices, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max
UNWIND range(1, num_devices) AS d_idx
CREATE (d:Device {
    imei: "IMEI_" + split(p.name, '_')[1] + "_" + d_idx,
    num:  "06" + toInteger(rand() * 89999999 + 10000000)
})
CREATE (p)-[:OWNS]->(d)


WITH d, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max,
     toInteger(rand() * (max_pings - min_pings) + min_pings) AS nb_pings_target


UNWIND range(1, nb_pings_target) AS p_idx
WITH d, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max,
     rand() AS r_lat, rand() AS r_lon, rand() AS r_time

CREATE (ping:Ping {
    
    location: point(
         r_lat * (lat_max - lat_min) + lat_min,
        r_lon * (lon_max - lon_min) + lon_min
    ),
    
    time: start_date + duration({seconds: toInteger(r_time * time_window_sec)})
})
CREATE (d)-[:GENERATED]->(ping)

[lvca]

I agree, it's useless. At least we need the inter timing of each step to understand how the total time is distributed. Also, there is no mention if indexes are used.

[ExtReMLapin]

OpenCypher Query Profile
========================

Execution Time: 8234,699 ms
Rows Returned: 278000

Execution Plan (Traditional):
+ DUMMY ROW (for standalone expressions)
+ WITH 500 AS nb_persons, 500 AS min_pings, 500 AS max_pings, datetime('2024-01-01T00:00:00') AS start_date, 7 * 24 * 60 * 60 AS time_window_sec, 48.80 AS lat_min, 48.90 AS lat_max, 2.25 AS lon_min, 2.40 AS lon_max (25 203μs)
+ UNWIND range(1, nb_persons) AS i (2 879μs)
+ WITH i, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max, rand() AS r_dev_distro (3 111μs)
+ WITH i, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max, CASEWHENr_dev_distro<0.90THEN1WHENr_dev_distro<0.97THEN2ELSE3END AS num_devices (1 260μs)
+ CREATE (63 182μs)
+ WITH p, num_devices, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max (1 743μs)
+ UNWIND range(1, num_devices) AS d_idx (2 140μs)
+ CREATE (50 322μs)
+ CREATE (55 202μs)
+ WITH d, min_pings, max_pings, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max, toInteger(rand() * max_pings - min_pings + min_pings) AS nb_pings_target (2 092μs)
+ UNWIND range(1, nb_pings_target) AS p_idx (360 098μs)
+ WITH d, start_date, time_window_sec, lat_min, lat_max, lon_min, lon_max, rand() AS r_lat, rand() AS r_lon, rand() AS r_time (243 157μs)
+ CREATE (5 177 500μs)
+ CREATE (2 105 395μs)