duckdb / duckdb/duckdb-spatial

RTREE index is not used

Open
#769 1 comment 0 reactions 1 assignee Claimed by @Maxxen View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.