Feature Requests item #2926187, was opened at 2010-01-05 12:45
Message generated for change (Comment added) made by skinkie
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482471&aid=2926187&group_id=56967
Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL - general
Group: None
Status: Open
Priority: 5
Private: No
Submitted By: Stefan de Konink (skinkie)
Assigned to: Nobody/Anonymous (nobody)
Summary: SQL: increase cost of a like operation
Initial Comment:
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 By: Stefan de Konink (skinkie)
Date: 2010-01-05 13:53
Message:
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 By: Martin Kersten (mlkersten)
Date: 2010-01-05 13:29
Message:
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.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=482471&aid=2926187&group_id=56967
------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev
_______________________________________________
Monetdb-bugs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
