Connect the Dots in Real-Time: Benchmarking geospatial join performance in GPU-Accelerated HeavyDB against CPU databases

While HEAVY.AI is a general purpose analytics platform, many of our customers’ key use cases are centered around geospatial data and workflows. Part of the reason for this lies in the scalable visualization capabilities of the system; by using GPUs for both querying and their original purpose of rendering data, we allow our users to make scalable and fully interactive maps of billions of points, lines, and polygons, no downsampling or pre-tiling required.

‍

But HEAVY.AI can do far more than just visualize big geospatial datasets; it also has the ability to rapidly query, manipulate, and join such data. HeavyDB, the core SQL engine at the heart of the HEAVY.AI platform, natively supports Open Geospatial Consortium (OGC) datatypes and operators, and can execute spatial operations with unprecedented speed.

‍

Of particular importance for geospatial workflows is spatial join performance, as fast spatial joins allow for ad-hoc and interactive fusion of multiple disparate spatial datasets based on an implicit spatial key. For example, retailers and CPGs might join customer address data to census block groups to get an indication of income and other attributes of their customers, to help improve inventory management or optimize store placement. In other cases, telcos might want to determine the distance between cell towers and customers’ home addresses as well as retail establishments like coffee shops, as distance between handset and tower is the largest determinant of coverage quality.

‍

Given the importance of spatial joins, we set about to test the performance of GPU-accelerated HeavyDB compared to five common databases used for spatial analysis: PostgreSQL, DuckDB, and Snowflake, Redshift and BigQuery. Snowflake, Redshift, and BigQuery natively support the OGC geospatial data types and operators needed, while PostgreSQL and DuckDB require the PostGIS and DuckDB spatial extensions, respectively.  Snowflake was tested using a Large instance costing $16/hr, Redshift on a 48 RPU serverless deployment costing $12/hr, and BigQuery using a 400 slot deployment costing $24/hr. Finally, HeavyDB, Postgres, and DuckDB were tested on a Grace Hopper GH200 server running in Vultr Cloud, available for $2.99/hr. As we’ve written elsewhere, the Grace Hopper and Grace Blackwell architectures with fast NVLink connections between CPU and GPU are highly advantageous for GPU-accelerated analytics workloads, as data that doesn’t fit in GPU VRAM can be quickly streamed across the bus from CPU. Particularly relevant for Postgres and DuckDB, the instance has a powerful 72-core Arm Neoverse V2 CPU, and we’ve found DuckDB to run significantly faster on this instance than much costlier X86 instances in past testing.

‍

Our benchmark queries can be bucketed into two categories: point-in-polygon joins and point-to-point distance joins.

‍

Point-in-Polygon Joins

‍

Point-in-polygon joins generally use the ST_CONTAINS or ST_INTERSECTS operators to merge (join) a table containing points (for example, tweets with locations, cell phone records) and a table containing polygon geometries (i.e. census block groups, cell coverage polygons). This generally allows enrichment of the points dataset with metadata from the polygons dataset, in addition to allowing rollup aggregations per polygon (i.e. per census block group).

‍

For example, a common use case for point-in-polygon joins is to take a dataset of interest with a geographic point attribute (i.e. tweets with locations, call data records, traffic tickets, etc) and roll them up to the level of a geographic region like country, state/province, census block group or equivalent, or even building. Note that generally the focal dataset does not already have this region info computed (i.e. it just has the point location and no metadata for geographic region), otherwise it would be easier and more performant to just use the pre-computed region attribute.

‍

As an example, the U.S. Geological Survey (USGS) in conjunction with UC San Diego recently released LiDAR-derived Digital Elevation Model (DEM) and Digital Surface Model (DSM) datasets for the areas impacted by the devastating Palisades and Eaton fires. We wanted to use this data to determine which structures were damaged by the fire, as a potential precursor to building a better fire risk model. To do this, we joined the DEM and DSM datasets, containing over 1B data points each, to Overture building footprints for the area, looking for areas in which the height of man-made structures was close to the base elevation height, suggesting a structure that was burned down. As can be seen in the map below generated from the HeavyImmerse notebook SQL query, the purple structures generally represent areas that were burned over.

‍

‍

With this example use case in mind, let’s proceed to review the benchmarks. To start with, here are the queries that were run in each system. Note that to isolate geospatial join performance as much as possible, simple count aggregates were computed, which also made it easy to ensure the correctness of results in each system by comparing the returned counts.  In a real-world scenario, we would generally be rolling up the data to the geographic region we are spatially joining to, similar to the query above joining elevations to building footprints.

‍

Q1. SELECT COUNT(*) FROM us_tweets_100m AS T1 JOIN usa_states AS T2 ON ST_CONTAINS(T2.geom, T1.location);

‍

Q2. SELECT COUNT(*) FROM us_tweets_100m JOIN usa_census_block_groups AS T2 ON ST_CONTAINS(T2.geom, T1.location);

‍

Q3. SELECT COUNT(*) FROM taxi_trips AS T1 JOIN nyc_taxi_zones AS T2 ON ST_CONTAINS(T2.geom, T1.dropoff_point);

‍

Q4. SELECT COUNT(*) FROM taxi_trips AS T1 JOIN nyc_buildings AS T2 ON ST_CONTAINS(T2.geom, T1.dropoff_point);

‍

Below is an overview of the datasets used in testing.

‍

Fact Tables (Point Datasets)

‍

‍

Dimension Tables (Polygon Datasets)

‍

‍

And below are the results of the testing. All times were measured as the average of 3 “warm” queries after an initial query was run on cold data, to eliminate disparities between data load times and storage subsystem performance of each database. HeavyDB, Snowflake, Redshift, and BigQuery do not require spatial indexes, while for Postgres and DuckDB indexes were added to the polygon/multipolygon columns in the dimension tables to achieve the denoted performance. When comparing systems, the additional overhead and operational complexity of adding and maintaining indexes should be considered when looking at end-to-end performance and administrative overhead.

‍

‍

As you can see, HeavyDB, running on a single GPU, is between 6X and 190X faster than the tested CPU data warehouses, with the results for Postgres not counted due to not being able to finish Q3 and Q4. The speed differences, beyond just numbers, point to the fact that fairly large geospatial joins can be run interactively in HeavyDB, allowing for quick ad-hoc analyses to be conducted employing various geospatial joins that would need to be run as a batch workflow in the other systems.

‍

It should also be noted that the costs of the three cloud data warehouses tested: Snowflake, Redshift, and BigQuery, were significantly higher than the cost of the HeavyDB GH200 instance on Vultr Cloud, making the overall price-performance ratios (HeavyDB speedup in the geometric mean of the runtime divided by cost ratio) between 27.3X and 337.0X. 

‍

Distance Joins

‍

Another relevant geospatial use case is joining datasets based on distance between geometries.  For example, imagine you are a retailer and want to join customer residential addresses to store locations to determine the location of the nearest store. With geospatial SQL, such use cases are as simple as a join using a  ST_DISTANCE or ST_DWITHIN predicate, although as we’ll see below, the performance you get varies wildly between databases.

‍

For simplicity, we ran the joins in their native 4326/WGS84 longitude/latitude projections, using a 0.1 distance threshold to approximate 10km. In reality, one would likely either convert the projection to Google Mercator or a local UTM zone, or use casts to GEOGRAPHY to filter the distance between points by a threshold in meters (and not degrees). However, while HeavyDB has a beta feature foron-the-fly accelerated hash joins using GEOGRAPHY-casted types (which has not yet been included in a release), other systems like Redshift and DuckDB do not support this. Therefore, we benchmarked  join performance using degree distances in the native 4326 projection for the time being.

‍

‍

Below are the datasets used in testing:

‍

Fact Tables (Point Datasets)

‍

‍

Q5. SELECT COUNT(*) FROM world_airports AS T1 JOIN world_airports AS T2 ON ST_DWITHIN(T1.geom, T2.geom, 0.01) AND T1.id > T2.id;

‍

Q6. SELECT COUNT(*) FROM us_tweets_100m AS T1 JOIN world_airports AS T2 ON ST_DWITHIN(T1.location, T2.geom, 0.01);

‍

Note that for Postgres and DuckDB, spatial indexes were added to all point geometry columns to accelerate joins.

‍

‍

Outside of HeavyDB, only Snowflake could run the second query, joining 100m tweet locations to 70K airport locations, in under ten seconds. Surprisingly, DuckDB was very slow in this test, taking over 8 minutes to even finish the self-join of the airports table, HeavyDB only taking 12 milliseconds and even Postgres taking 726 milliseconds. Interestingly, while Redshift was the fastest system outside of HeavyDB for point-in-polygon joins, it fared quite poorly in this point distance test, with query Q6 not returning.

‍

Again, HeavyDB running on the GH200 instance yields significant price-performance advantages over the other systems, 68.4X using the geometric means of a baseline against the next fastest system, Snowflake.

‍

Conclusion

‍

In conclusion, the above benchmarks demonstrate the substantial advantages of GPU acceleration for geospatial join operations. HeavyDB, running on a cost-effective Grace Hopper GH200 instance, consistently outperformed leading CPU-based databases like PostgreSQL, DuckDB, Snowflake, Redshift, and BigQuery across both point-in-polygon and distance join scenarios, achieving speedups ranging from 7X to over 175X. This raw performance, combined with significantly lower instance costs of HeavyDB running on the GH200 compared to the tested cloud data warehouses, resulted in dramatic price-performance gains, often exceeding 100X. Ultimately, HeavyDB's ability to execute complex spatial joins in seconds, rather than minutes or hours, transforms large-scale geospatial analysis from a batch process into an interactive exploration, empowering users to connect the dots in real-time and derive insights more rapidly and effortlessly than was possible before.