Bug 2516 - SQL: increase cost of a like operation
Summary: SQL: increase cost of a like operation
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: x86 (i?86) Linux
: --- enhancement
Assignee: SQL devs
URL: http://sourceforge.net/support/tracke...
Keywords:
Depends on:
Blocks:
 
Reported: 2010-01-05 12:45 CET by Stefan de Konink
Modified: 2016-04-11 11:44 CEST (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Stefan de Konink 2010-01-05 12:45:25 CET
It seems in a query like:
explain select kvk from kvk where length('hello') = length(bedrijfsnaam) and  bedrijfsnaam like '%hello%';

The like operation is before the length, I have attached the mal explain of it. This is the plan:
project (
| select (
| | table(sys.kvk) [ kvk.kvk, kvk.bedrijfsnaam, kvk.%TID% NOT NULL ]
| ) [ length(kvk.bedrijfsnaam) = length('hello'), convert(kvk.bedrijfsnaam) like convert('%hello%') ]
) [ kvk.kvk ]

If like is indeed before the length equality function (it seems to be finished after the 2nd barrier), shouldn't the cost for the like operation be increased?
Comment 1 Martin Kersten cwiconfidential 2010-01-05 13:29:49 CET
Optimizers generally do not have any knowlegde about the cost of (user-defined) functions. MonetDB does not use a cost-based reordering of the plan either to benefit from this specific case.
Comment 2 Stefan de Konink 2010-01-05 13:53:27 CET
It currently does do a cost assessment in exp_count(int *cnt, int seqnr, sql_exp *e), sql/src/server/rel_optimizer.mx. Is it there where a preference is set for the like operation?

Never the less, I do see your point regarding (user-defined) functions, what I do not understand is that the actual operations are also (user defined) function, for them a cost is set. It seems that the long term goal could be the dynamic cost evaluation of each function with respect to the dataset starting with the heuristic as it is set now. Regarding to this specific case what prevents adding a specific cost to the length function?
Comment 3 Pseudo user for Sourceforge import 2010-05-04 09:32:09 CEST
This bug was previously known as tracker item 2926187 at http://sourceforge.net/support/tracker.php?aid=2926187

The original assignee of this bug does not have
   an account here. Reassigning to the default assignee
   for the component, bugs-sql@monetdb.org.
   Previous assignee was nobody@users.sourceforge.net.