Bug 2516

Summary: SQL: increase cost of a like operation
Product: SQL Reporter: Stefan de Konink <skinkie>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: enhancement    
Priority: ---    
Version: -- development   
Hardware: x86 (i?86)   
OS: Linux   
URL: http://sourceforge.net/support/tracker.php?aid=2926187

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.