influxdata / influxdata/influxdb
Slow geo query
- Dominant language
- Rust
- Stars
- 31.7k
- Forks
- 3.7k
- Avg merge
- 13h 37m
- Merged PRs (30d)
- 8
Description
## Describe Development Experience Issue:
Hi, so I've noticed that spatial querying is extremely slow on my machine and was wondering if that's normal for Influx or am I'm doing something wrong.
I have a [dataset of ships' positions from NOAA](https://coast.noaa.gov/htdata/CMSP/AISDataHandler/2020/index.html), one day of which I downloaded and saved in InfluxDB.
Unfortunately when querying the data I get results after extremely long time. I'd want to query data at `level=24` and time range of 24h, but as things are now it takes 72s to query ships within a bounding box and a time range of 1 minute.
### Steps to reproduce:
1. Install InfluxDB 2.7.6 from Docker
2. Download [ships' positions from one day](https://coast.noaa.gov/htdata/CMSP/AISDataHandler/2020/index.html)
3. Write them to influx using python `InfluxDBClient`:
```python
write_api.write(bucket=bucket_name, org=organization_id,
record=df,
data_frame_measurement_name="vessels_ais_31_12",
data_frame_tag_columns=data_frame_tag_columns,
data_frame_timestamp_column="BaseDateTime",
)
```
4. Create a new bucket with just spatial index and basic information:
```python
level = 24
for start, end in generate_time_ranges_for_day(day, interval):
flux_query = f"""
import "experimental/geo"
from(bucket: "{raw_data_bucket}")
|> range(start: {start}, stop: {end})
|> filter(fn: (r) => r._measurement == "vessels_ais_31_12")
|> filter(fn: (r) => r._field == "LAT" or r._field == "LON")
|> geo.shapeData(latField: "{lat_field_name}", lonField: "{lon_field_name}", level: {level})
|> to
(bucket: "{indexed_data_bucket}", tagColumns: ["s2_cell_id", "MMSI"], fieldFn: (r) => ({{"lat": r.lat, "lon": r.lon}}))
"""
```

5. Query the data:
```python
region = {{
minLat: {min_lat},
maxLat: {max_lat},
minLon: {min_lon},
maxLon: {max_lon},
}}
from(bucket: "{bucket}")
|> range(start: {start_date}, stop: {stop_date})
|> filter(fn: (r) => r._measurement == "vessels_ais_31_12")
|> geo.filterRows(region: region, level: {level}, strict: {strict})
"""
```
### Desired result:
## Ideal
Returns result from **24h period** on **level 24** in less than 10 seconds.
## Less ideal
Returns ideal result in less than 10 minutes.
### Actual result:
Range of just **one minute**
On 5GB memory:
Lvl 10 - 72s
Lvl 11 - 122s
Lvl 12 - 113s
Lvl 13 - 134s
Lvl 14 - 122s
Lvl 15 - 139s
Lvl 16 - 222s
Lvl 17 - 573s
Lvl 18 - timeout after 600 seconds
On 16GB memory:
Lvl 10 - 109s
Lvl 11 - 109s
Lvl 12 - 104s
Lvl 13 - 105s
Lvl 14 - 110s
Lvl 15 - 131s
Lvl 16 - 220s
Lvl 17 - 536s
Lvl 18 - timeout after 600 seconds
## Hardware Environment:
- Package: laptop, localhost, setup on docker. I used `influx:latest`, but it was some time ago. It's influx version `2.7.6`, CLI vesion `2.7.3`.
- CPU: AMD Ryzen 7 5700U
- Memory: 5GB was allocated to the image, now testing on 16GB, although container never ran out of memory while querying this data.
## Operating System:
Laptop:
Ubuntu 22.04.4 LTS x86_64
Docker image:
Linux 36a415262a30 6.4.16-linuxkit #1 SMP PREEMPT_DYNAMIC Fri Nov 10 14:51:57 UTC 2023 x86_64 GNU/Linux
## Code Editing Tool:
PyCharm and influx in browser control panel
## Build Environment:
Docker. influx:latest. Influx version `2.7.6`, CLI vesion `2.7.3`.
Contributor guide
Research direction
The issue names no repository files, tests, or entry points. Start by reproducing the Flux geo query with the NOAA dataset and the documented InfluxDB 2.7.6 Docker setup; done means establishing whether the reported latency is reproducible and identifying a documented or testable performance improvement.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- docker, python
- Domain
- databases, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 25/100