dolthub / dolthub/dolt

Spatial Indexes may be slow for certain queries

Open
#5,509 0 comments 0 reactions 0 assignees View on GitHub
performance sql
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 8h
Merged PRs (30d)
120

Description

Depending on the table and the query, performance might not be great for Spatial Indexes.
Problematic situations:
- Large selections
- Filters that select large portions of rows are slower through Spatial index
- Large geometries
- Tables with large polygons (especially ones that cross axis boundaries) tend to be slower

There are a couple optimization options, some are harder than others, and some may break compatibility between dolt versions.
Potential Optimizations:
- Level Pruning
- For every query, we generate 65 ranges; many tables don't have objects that large,
- Covering Indexes
- Spatial Indexes are non-covering, which forces an extra level of indirection, slowing down lookups
- Geometry Decomposition
- Can further minimize the number of false positives by breaking down large objects into smaller ones

Contributor guide

No contributing guide indexed for this repository

Research direction

The issue names no files, tests, or entry points. Start by locating the spatial-index implementation and measuring the listed large-selection and large-geometry cases; done requires selecting and validating an optimization without breaking compatibility between Dolt versions.

Written by the indexing model from the issue text.

Assessment

Tech stack
go
Domain
databases, performance
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.