New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Bug: No index is used when using time::now() #3950
Labels
Comments
emmanuel-keller
added
topic:indexing
This is related to indexing and full-text search
and removed
triage
This issue is new
labels
May 3, 2024
Thanks for reporting this. Indeed, the query planner does not trigger the index due to the complex operand |
There is currently a workaround. DEFINE INDEX stamp ON articles FIELDS stamp_create;
LET $dt = time::now() - 5d;
SELECT id,stamp_create FROM articles WHERE stamp_create > $dt EXPLAIN; [
{
detail: {
plan: {
from: {
inclusive: false,
value: '2024-05-05T14:57:33.635Z'
},
index: 'stamp',
to: {
inclusive: false,
value: NONE
}
},
table: 'articles'
},
operation: 'Iterate Index'
},
{
detail: {
type: 'Store'
},
operation: 'Collector'
}
] |
2 tasks
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Labels
I have a simple table "articles" with the fields "id" and "stamp_create". It is "schemaless".
I run a SQL query on the field "stamp_create" and want to see all "ids" of the last 5 days.
select id,stamp_create FROM articles WHERE stamp_create > (time::now() - 5d) LIMIT 5;
This also works in the end result:
-- Query 1 (execution time: 1.233700179s)
I have an index on the column "stamp_create":
However, this index is not used in the SQL query:
A appears instead:
Unsupported value: time::now()
Without using the index on the "datetime" field in combination with the "time::*()" functions, it is very slow when querying large records (250K in this case). We are in the high second range here, not the millisecond range.
If you add sorting with "ORDER BY", where no index is currently used (see #2799 or #3746), the performance looks very bad. Time calculations and time limits in particular are very important for a large DB.
P.S. The documentation could include more examples of Datetime calculations using functions, such as adding or subtracting times, etc. Under "Datetime comparison" there is only a simple example without the use of functions.
SurrealDB version
Running 1.4.2 for linux on x86_64 (Ubuntu)
The text was updated successfully, but these errors were encountered: