duckdb / duckdb/duckdb-spatial
RTREE index is not used
- Dominant language
- C
- Stars
- 708
- Forks
- 96
- Avg merge
- 1d 21h
- Merged PRs (30d)
- 5
Description
### What happens?
If I create a table with a geometry column and an id column RTREE index is not used:
When, if I create the table with only the geometry column, then the RTREE index is used.
### To Reproduce
```
INSTALL spatial;
LOAD spatial;
CREATE TABLE IF NOT EXISTS t1 (
id LONG PRIMARY KEY,
geom GEOMETRY
);
-- Create a table with 10_000_000 random points
INSERT INTO t1(id, geom)
SELECT row_number() OVER (), point
FROM st_generatepoints({min_x: 0, min_y: 0, max_x: 100, max_y: 100}::BOX_2D, 10_000, 1337);
-- Create an index on the table.
CREATE INDEX my_idx ON t1 USING RTREE (geom);
-- Perform a query with a "spatial predicate" on the indexed geometry column
-- Note how the second argument in this case, the ST_MakeEnvelope call is a "constant"
SELECT count(*) FROM t1 WHERE ST_Within(geom, ST_MakeEnvelope(45, 45, 65, 65));
-- RTREE is not used
EXPLAIN SELECT count(*) FROM t1 WHERE ST_Within(geom, ST_MakeEnvelope(45, 45, 65, 65));
```
### OS:
MacOS
### DuckDB Version:
1.5
### DuckDB Client:
CLI
### Hardware:
_No response_
### Full Name:
Maurizio Merli
### Affiliation:
Xthema
### Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?
- [x] Yes, I have
### Did you include all code required to reproduce the issue?
- [x] Yes, I have
### Did you include all relevant data sets for reproducing the issue?
Yes
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.