Well there are only two possible execution orders. The RTO is obviously
picking the wrong one. But the static optimizer will get it right in this
case.
The RTO should not be used by default. The proper use is to run it head to
head with the static optimizer on very complex queries to see if a better
join order can be identified than what is chosen use static heuristics
(range counts). And then if a better order is indeed identified by the
RTO, hand-code the query in that order and use a query hint to turn off all
re-ordering, static or otherwise. You would never want to write an
application that uses the RTO by default on every query.
Fair enough, but, assuming my users ultimately won't know anything about RTO, I figure I have to enable it for all queries or disable it for all queries. I was thinking enabling it in all cases was the right thing because it'd significantly improve performance for some queries. If its actively worse in some cases maybe I'm just wrong - it should be off for all queries.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Personally I would disable it by default. The static optimizer will get
the right answer most of the time and it will get it a lot faster than the
RTO. The RTO is more for deeper analysis on individual queries.
Fair enough, but, assuming my users ultimately won't know anything about
RTO, I figure I have to enable it for all queries or disable it for all
queries. I was thinking enabling it in all cases was the right thing
because it'd significantly improve performance for some queries. If its
actively worse in some cases maybe I'm just wrong - it should be off for
all queries.
This query is quite quick (250ms):
but its isomorph is much slower (20s):
Last edit: manybubbles 2015-03-16
Can you please send along the explain output from the Blazegraph workbench
on those two queries?
Mike Personick
On Mon, Mar 16, 2015 at 2:36 PM, manybubbles manybubbles@users.sf.net
wrote:
Last edit: Mike Personick 2015-03-17
https://gist.github.com/nik9000/07a2b1425acd9221b011 is the fast one.
The slow one doesn't return from explain.
In both cases the runtime query optimizer is on.
Well there are only two possible execution orders. The RTO is obviously
picking the wrong one. But the static optimizer will get it right in this
case.
The RTO should not be used by default. The proper use is to run it head to
head with the static optimizer on very complex queries to see if a better
join order can be identified than what is chosen use static heuristics
(range counts). And then if a better order is indeed identified by the
RTO, hand-code the query in that order and use a query hint to turn off all
re-ordering, static or otherwise. You would never want to write an
application that uses the RTO by default on every query.
Mike Personick
On Mon, Mar 16, 2015 at 3:07 PM, manybubbles manybubbles@users.sf.net
wrote:
New information: turning off the runtime query optimizer makes this faster. Ticking the box in the workbench makes it all much slower.
I'm actually not sure that 20 seconds was accurate - now its not coming back at all....
This is not really a query that would benefit from the RTO. The static
join optimizer will get the order correct on its own with this one.
Mike Personick
On Mon, Mar 16, 2015 at 2:46 PM, manybubbles manybubbles@users.sf.net
wrote:
Fair enough, but, assuming my users ultimately won't know anything about RTO, I figure I have to enable it for all queries or disable it for all queries. I was thinking enabling it in all cases was the right thing because it'd significantly improve performance for some queries. If its actively worse in some cases maybe I'm just wrong - it should be off for all queries.
Personally I would disable it by default. The static optimizer will get
the right answer most of the time and it will get it a lot faster than the
RTO. The RTO is more for deeper analysis on individual queries.
Mike Personick
On Mon, Mar 16, 2015 at 3:13 PM, manybubbles manybubbles@users.sf.net
wrote:
Got it!