You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Date: 2016-04-06 17:47:03 +0200
From: Richard Hughes <<richard.monetdb>>
To: MonetDB5 devs <>
Version: -- development
Last updated: 2016-12-21 13:07:51 +0100
Comment 22018
Date: 2016-04-06 17:47:03 +0200
From: Richard Hughes <<richard.monetdb>>
Created attachment 393
Patch
The attached patch makes expressions of the form "col LIKE 'x'" run approximately 8 times faster.
This seems like a totally useless optimization, except that the ILIKE operator is by far the fastest way to do a case-insensitive string comparison. Some benchmarks:
select count() from t where lower(x)='x': 159s
select count() from t where x ilike 'x' [pre-patch]: 4.1s
select count(*) from t where x ilike 'x' [with patch]: 0.520s
The implementation is not very neat but the performance gain is worth it to us.
Another idea so that I don't forget:
If pattern ends in % and has no other _ or %, use strncmp or strncasecmp.
Comment 22022
Date: 2016-04-07 12:03:52 +0200
From: Richard Hughes <<richard.monetdb>>
That optimization is actually already covered by the 'use_re' code path. See re_simple() for the conditions under which it is invoked and re_match_ignore() for the matcher.
Adding to my benchmarks above:
select count() from t where x ilike 'x%': 0.411s
select count() from t where x ilike '%x%': 3.8s
I'm slightly curious about how the first one ended up even faster than my implementation. It's possible that glibc's strncasecmp is faster than strcasecmp I suppose.
Here's a list of expressions that I would consider to be theoretically amenable to a simplified matcher but which still fall back to the pcre matcher:
'x%' escape ''
'x' escape ''
'%x'
I wrote this patch in order to speed up a set of queries I have that are very frequently run. I got my 8x speed-up for my use case and so I'm happy - anything beyond that is a bonus.
Date: 2016-04-06 17:47:03 +0200
From: Richard Hughes <<richard.monetdb>>
To: MonetDB5 devs <>
Version: -- development
Last updated: 2016-12-21 13:07:51 +0100
Comment 22018
Date: 2016-04-06 17:47:03 +0200
From: Richard Hughes <<richard.monetdb>>
Created attachment 393
Patch
The attached patch makes expressions of the form "col LIKE 'x'" run approximately 8 times faster.
This seems like a totally useless optimization, except that the ILIKE operator is by far the fastest way to do a case-insensitive string comparison. Some benchmarks:
select count() from t where lower(x)='x': 159s
select count() from t where x ilike 'x' [pre-patch]: 4.1s
select count(*) from t where x ilike 'x' [with patch]: 0.520s
The implementation is not very neat but the performance gain is worth it to us.
Comment 22019
Date: 2016-04-06 21:24:39 +0200
From: @sjoerdmullender
Interesting idea. I'll take a closer look.
Another idea so that I don't forget:
If pattern ends in % and has no other _ or %, use strncmp or strncasecmp.
Comment 22022
Date: 2016-04-07 12:03:52 +0200
From: Richard Hughes <<richard.monetdb>>
That optimization is actually already covered by the 'use_re' code path. See re_simple() for the conditions under which it is invoked and re_match_ignore() for the matcher.
Adding to my benchmarks above:
select count() from t where x ilike 'x%': 0.411s
select count() from t where x ilike '%x%': 3.8s
I'm slightly curious about how the first one ended up even faster than my implementation. It's possible that glibc's strncasecmp is faster than strcasecmp I suppose.
Here's a list of expressions that I would consider to be theoretically amenable to a simplified matcher but which still fall back to the pcre matcher:
'x%' escape ''
'x' escape ''
'%x'
I wrote this patch in order to speed up a set of queries I have that are very frequently run. I got my 8x speed-up for my use case and so I'm happy - anything beyond that is a bonus.
Comment 24685
Date: 2016-11-15 14:00:23 +0100
From: MonetDB Mercurial Repository <>
Changeset 35d404f6597c made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=35d404f6597c
Changeset description:
Comment 24686
Date: 2016-11-15 14:01:13 +0100
From: @sjoerdmullender
This is in the Dec2016 branch.
The text was updated successfully, but these errors were encountered: