Issue 7777 - Filtering on modifyTimestamp is slower with index
Summary: Filtering on modifyTimestamp is slower with index
Status: VERIFIED SUSPENDED
Alias: None
Product: OpenLDAP
Classification: Unclassified
Component: slapd (show other issues)
Version: 2.4.38
Hardware: All All
: --- enhancement
Target Milestone: ---
Assignee: OpenLDAP project
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-01-09 11:02 UTC by Raphael Ouazana
Modified: 2023-10-23 16:49 UTC (History)
1 user (show)

See Also:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description Raphael Ouazana 2014-01-09 11:02:27 UTC 
Full_Name: Raphael Ouazana
Version: 2.4.38
OS: Linux
URL: ftp://ftp.openldap.org/incoming/
Submission from: (NULL) (88.173.78.196)


Hi,

I have a case where using modifyTimestamp index is far slower than with no
index.

First with indexing:

###Caching

$ time ldapsearch -b <people> -D <manager> -W
# search result
search: 2
result: 0 Success

# numResponses: 293886
# numEntries: 293885

real	0m12.153s
user	0m7.805s
sys	0m1.888s

### Request

$ time ldapsearch -b <people> -D <manager> -W
'(&(modifyTimestamp>=20131023163804Z)(modifyTimestamp<=20131123163804Z))'

# search result
search: 2
result: 0 Success

# numResponses: 24277
# numEntries: 24276

real	0m23.441s
user	0m0.554s
sys	0m0.073s


Now without modifyTimestamp index:

###Caching

$ time ldapsearch -b <people> -D <manager> -W
# search result
search: 2
result: 0 Success

# numResponses: 293886
# numEntries: 293885

real	0m11.627s
user	0m7.354s
sys	0m1.772s

### Request

$ time ldapsearch -b <people> -D <manager> -W
'(&(modifyTimestamp>=20131023163804Z)(modifyTimestamp<=20131123163804Z))'
# search result
search: 2
result: 0 Success

# numResponses: 24277
# numEntries: 24276

real	0m0.701s
user	0m0.569s
sys	0m0.070s

As you can see, without indexing the request takes less than 1s, while with
index it takes more than 20s. I think it is not the expected result.

Regards,
Rapha�l Ouazana.
Comment 1 Michael Ströder 2014-01-09 15:26:10 UTC 
The ITS is not for support requests. You should post things like this on the
openldap-technical mailing list.

raphael.ouazana@linagora.com wrote:
> I have a case where using modifyTimestamp index is far slower than with no
> index.
> [..]
> '(&(modifyTimestamp>=20131023163804Z)(modifyTimestamp<=20131123163804Z))'

You might wanna read this old thread:

http://www.openldap.org/lists/openldap-technical/201204/msg00116.html

Try to use this filter instead:

(&(modifyTimestamp>=20131023163804Z)(!(modifyTimestamp>=20131123163804Z)))

Ciao, Michael.
Comment 2 Raphael Ouazana 2014-01-09 16:17:56 UTC 
Hi,

Le 09/01/2014 16:26, Michael Ströder a écrit :
> The ITS is not for support requests. You should post things like this 
> on the
> openldap-technical mailing list.
> 
> raphael.ouazana@linagora.com wrote:
>> I have a case where using modifyTimestamp index is far slower than 
>> with no
>> index.
>> [..]
>> '(&(modifyTimestamp>=20131023163804Z)(modifyTimestamp<=20131123163804Z))'
> 
> You might wanna read this old thread:
> 
> http://www.openldap.org/lists/openldap-technical/201204/msg00116.html

Interesting, seems you already found this issue.

> Try to use this filter instead:
> 
> (&(modifyTimestamp>=20131023163804Z)(!(modifyTimestamp>=20131123163804Z)))

A little better, but not really: 12s.
In fact 
(&(!(!(modifyTimestamp<=20131123163804Z)))(!(modifyTimestamp<=20131023163804Z)))
is really better (<1s), but it only means I deactivate indexing.

I think this is a bug, because if it is faster not to use an index for 
a specific request, OpenLDAP should do it.

Regards,
Raphaël Ouazana.
Comment 3 Quanah Gibson-Mount 2021-06-07 17:12:57 UTC 
Could attempt optimizing search code when both a lower and upper bound are provided.

Would still be problematic for >= or <= only searches.
Comment 4 Quanah Gibson-Mount 2023-10-23 16:49:09 UTC 
patches welcome
generally don't index timestamps.