Skip to content
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.

Sign up for GitHub

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

Jump to bottom

Bug: No index is used when using time::now() #3950

Closed
admtech opened this issue Apr 26, 2024 · 2 comments · Fixed by #4022
Closed

Bug: No index is used when using time::now() #3950

admtech opened this issue Apr 26, 2024 · 2 comments · Fixed by #4022
Assignees
@emmanuel-keller
Labels
bug Something isn't working topic:indexing This is related to indexing and full-text search

Comments

@admtech
Copy link

admtech commented Apr 26, 2024 •
edited

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)

[
	{
		id: articles:4762033384,
		stamp_create: '2024-04-24T10:30:00Z'
	},
	{
		id: articles:31510144996,
		stamp_create: '2024-04-25T12:36:00Z'
	}
]

I have an index on the column "stamp_create":

INFO FOR TABLE articles;
-- Query 1 (execution time: 842.008µs)
{
	events: {},
	fields: {
		stamp_create: 'DEFINE FIELD stamp_create ON articles TYPE datetime PERMISSIONS FULL'
	},
	indexes: {
		stamp: 'DEFINE INDEX stamp ON articles FIELDS stamp_create'
	},
	lives: {},
	tables: {}
}

However, this index is not used in the SQL query:

select id,stamp_create FROM articles WHERE stamp_create > (time::now() - 5d) LIMIT 5 EXPLAIN FULL;
-- Query 1 (execution time: 1.268744763s)
[
	{
		detail: {
			table: 'articles'
		},
		operation: 'Iterate Table'
	},
	{
		detail: {
			reason: 'Unsupported value: time::now()'
		},
		operation: 'Fallback'
	},
	{
		detail: {
			type: 'Store'
		},
		operation: 'Collector'
	},
	{
		detail: {
			count: 2
		},
		operation: 'Fetch'
	}
]

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.

select id,stamp_create FROM articles WHERE stamp_create > (time::now() - 5d) ORDER BY stamp_create DESC LIMIT 5 EXPLAIN FULL;
-- Query 1 (execution time: 1.251594963s)
[
	{
		detail: {
			table: 'articles'
		},
		operation: 'Iterate Table'
	},
	{
		detail: {
			reason: 'Unsupported value: time::now()'
		},
		operation: 'Fallback'
	},
	{
		detail: {
			type: 'Store'
		},
		operation: 'Collector'
	},
	{
		detail: {
			count: 2
		},
		operation: 'Fetch'
	}
]

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)
@admtech admtech added bug Something isn't working triage This issue is new labels Apr 26, 2024
@emmanuel-keller emmanuel-keller self-assigned this May 3, 2024
@emmanuel-keller 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
@emmanuel-keller
Copy link
Contributor

Thanks for reporting this. Indeed, the query planner does not trigger the index due to the complex operand (time::now() - 5d). We are going to open a PR soon to fix this.

@emmanuel-keller
Copy link
Contributor

emmanuel-keller commented May 10, 2024 •
edited

There is currently a workaround.
If you store the datetime in a variable, the query planner will trigger the index.

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'
	}
]

@emmanuel-keller emmanuel-keller mentioned this issue May 10, 2024
Merged
2 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@emmanuel-keller emmanuel-keller

Labels
bug Something isn't working topic:indexing This is related to indexing and full-text search
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Query planner should evaluate non boolean expressions surrealdb/surrealdb
2 participants
@emmanuel-keller @admtech