vector_top_k JOIN with regular table causes pathological query plan (3700x slower than subquery)
Maintainers usually reply within 11 days
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 38/100
- Issue type
- Bug
- Clarity
- Mostly clear
- Activity status
- Stale
- Domain
- databases, performance
Research direction
Start by running the SQL reproduction and inspecting SQLite's virtual-table planner behavior around vector_top_k's xBestIndex. Compare EXPLAIN QUERY PLAN and profiler results for JOIN versus CROSS JOIN and the subquery workaround. Done means xFilter is invoked once rather than once per qualifying entry, with a reproducible regression test.
Written by the indexing model from the issue text.
Description
Summary
When joining vector_top_k() with a regular table using JOIN ... ON, the SQLite query planner inverts the join order when the regular table has WHERE predicates. This causes the virtual table xFilter to be called once per row of the regular table instead of once total, resulting in catastrophic performance degradation.
Reproduction
Database: ~12,000 entries with 512-dim float32 embeddings, WAL mode.
-- Index definition
CREATE INDEX idx_entries_embedding ON entries (
libsql_vector_idx(embedding, 'metric=cosine', 'compress_neighbors=float8', 'max_neighbors=50')
);
-- FAST (10ms): vector_top_k alone
SELECT id FROM vector_top_k('idx_entries_embedding', vector32(?), 50);
-- FAST (10ms): JOIN without WHERE clause on entries
SELECT e.id FROM vector_top_k('idx_entries_embedding', vector32(?), 50) AS v
JOIN entries AS e ON e.rowid = v.id;
-- SLOW (26,000ms, 100% CPU): JOIN with WHERE clause on entries
SELECT e.id FROM vector_top_k('idx_entries_embedding', vector32(?), 50) AS v
JOIN entries AS e ON e.rowid = v.id
WHERE e.superseded_by IS NULL;
-- FAST (6ms): CROSS JOIN forces correct order
SELECT e.id FROM vector_top_k('idx_entries_embedding', vector32(?), 50) AS v
CROSS JOIN entries AS e ON e.rowid = v.id
WHERE e.superseded_by IS NULL;
-- FAST (11ms): Subquery avoids join entirely
SELECT * FROM entries
WHERE rowid IN (SELECT id FROM vector_top_k('idx_entries_embedding', vector32(?), 50))
AND superseded_by IS NULL;
Analysis
When the entries table has a WHERE predicate (superseded_by IS NULL), the query planner decides to scan entries first and probe vector_top_k in the inner loop. With ~11,500 qualifying rows, this means ~11,500 ANN searches instead of 50 rowid lookups.
V8 profiler confirms 99.9% of CPU time is spent in the libsql native module executing repeated vector searches.
The root cause appears to be that vector_top_k's xBestIndex does not report a high enough cost to prevent the planner from placing it in the inner loop when there are competing predicates on the joined table.
Workarounds
- Use
CROSS JOINinstead ofJOIN(forces left-to-right evaluation) - Use
WHERE rowid IN (SELECT id FROM vector_top_k(...))instead of JOIN - Split into two application-level queries
Environment
- libsql native: 0.5.22
- @libsql/client: 0.17.0
- macOS arm64 (Apple M2)
- Node.js v25.5.0
Notes
This is the same class of issue documented for FTS5 virtual tables on the SQLite forum:
- https://sqlite.org/forum/info/509bdbe534f58f20 (FTS5 JOIN: 170s vs subquery: 439ms)
- https://sqlite.org/forum/info/81e6aa8343402ba5 (virtual table covering index limitations)
The Turso DiskANN blog post (https://turso.tech/blog/approximate-nearest-neighbor-search-with-diskann-in-libsql) shows the WHERE rowid IN pattern first, suggesting awareness of this issue.
- Dominant language
- C
- Stars
- 17.3k
- Forks
- 562
- Avg merge
- 10d 15h
- Merged PRs (30d)
- 1
Getting set up
- Ships a Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from tursodatabase/libsql
-
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
tursodatabase/libsql#2320 ·
Maintainers usually reply within 11 days
-
`cloudflare` feature: hrana streams are never closedPossibly taken @pcbeingused333 claimed this 4 days ago. Open
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
tursodatabase/libsql#2312 ·
Maintainers usually reply within 11 days
-
bottomless: finished S3 upload tasks are never reaped, so sqld memory grows by ~10 KB per WAL batchPossibly taken @pavanmanishd claimed this 25 days ago. Open
Difficulty 2/5 1-3 hours Newbie friendliness 83/100
tursodatabase/libsql#2298 · 1 comment ·
Maintainers usually reply within 11 days
-
Rust local Row::get_value truncates embedded NUL TEXT and panics on malformed UTF-8 (0.9.30)Possibly taken @Sanjays2402 claimed this 35 days ago. Open
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
tursodatabase/libsql#2285 ·
Maintainers usually reply within 11 days
-
libsql-ffi: broken builds under Nix on macOSPossibly taken @frectonz claimed this 85 days ago. Open
Difficulty 2/5 1-3 hours Newbie friendliness 86/100
tursodatabase/libsql#2275 ·
Maintainers usually reply within 11 days
All issues in tursodatabase/libsql
Similar issues
-
Difficulty 1/5 Under an hour Newbie friendliness 92/100
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
JulianKemmerer/PipelineC#395 ·
-
upstream-blocked
Difficulty 2/5 1-3 hours Newbie friendliness 65/100
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 70/100
libretro/parallel-n64#858 ·