db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6267) Add ability to compactly specify a complete query plan in an optimizer override.
Date Wed, 03 Jul 2013 13:11:21 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6267?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13698930#comment-13698930
] 

Rick Hillegas commented on DERBY-6267:
--------------------------------------

Hi Bryan,

Thanks for thinking about this issue. It is always good to have an extra set of eyes on a
new feature. I understand that you're uneasy about something but I'm not clear on what it
is. Maybe I will understand your uneasiness better if we exchange some more comments on this
issue.

The optimizer has always been mysterious to me. However, I have learned a lot about the optimizer
by working on this issue and its close relatives. One peculiar fact about the Derby optimizer
stands out: For sixteen years there has been no picture of a candidate plan being considered
by the optimizer. Instead, the optimizer holds two controlling structures (the Optimizable
list and the join order array). How those two structures relate to one another and describe
a candidate plan is hidden in assumptions scattered through the optimizer code. The closest
thing we had to a description of a plan was the actual runtime graph of executable ResultSets
which emerges after code generation.

Talking about an actual query plan is awkward if all you have is the graph of generated ResultSets.
Talking about an alternative plan is impossible because you don't even have a graph of ResultSets
to point to.

At this point I think that we have gone from a situation which no-one understands to a situation
which at least one person (myself) sort-of understands. I fully agree with you that the notation
I've adopted is cryptic and idiosyncratic. I'd welcome discussing how we can improve that
notation so that we end up with something which everyone understands.

I also share your concerns that optimizer overrides are a poor substitute for actually fixing
the optimizer. However, in other databases, overrides have proven themselves to be a useful
workaround to get customers up and running before you can get them a real fix. In my experience,
every improvement to the optimizer breaks someone's old query plan. Overrides give us a safety
net so that we can evolve our optimizer.

Having a compact description of a plan shape gives us the ability to build an optimizer which
learns. We now have a compact handle to which we can bind facts about the runtime behavior
of a plan. I think that's a useful step toward building a feedback loop between the optimizer
and the execution engine.

I would like to understand more about your uneasiness. Does it have something to do with either
of these concerns:

1) The proposed notation is cryptic and idiosyncratic.

2) We should not document this new notation but just leave it as a backdoor for tech support.

Thanks,
-Rick

                
> Add ability to compactly specify a complete query plan in an optimizer override.
> --------------------------------------------------------------------------------
>
>                 Key: DERBY-6267
>                 URL: https://issues.apache.org/jira/browse/DERBY-6267
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Rick Hillegas
>              Labels: derby_triage10_11
>         Attachments: derby-6267-01-ac-compactSyntax.diff, derby-6267-01-ad-compactSyntax.diff,
derby-6267-01-ae-compactSyntax.diff
>
>
> It would be nice to be able to override the optimizer's choice and specify a complete
query plan using the compact summary syntax output by XMLOptTrace. Given how the optimizer
handles a statement, this would require binding a query plan at the query block level. Two
obvious candidates for such a feature are:
> 1) Extend the use of DERBY-PROPERTIES in the comments of a query.
> 2) Add an extra clause to query blocks. The clause would have to be a clearly marked
Derby extension.
> (1) might look like this (here we add a new "fullQueryPlan" property):
> select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a
> where t.tablename = c.columnname and c.columnname = a.alias
> -- DERBY-PROPERTIES fullQueryPlan = (SYSCOLUMNS_HEAP # SYSALIASES_INDEX1) # SYSTABLES_INDEX1
> union all
> select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a, sys.syssequences
s
> where t.tablename = c.columnname and c.columnname = a.alias and a.alias = s.sequencename
> -- DERBY-PROPERTIES fullQueryPlan = ((SYSCOLUMNS_HEAP # SYSTABLES_INDEX1) # SYSALIASES_INDEX1)
# SYSSEQUENCES_INDEX2
> ;
> (2) might look like this (here we add a new "using derby join order" clause):
> select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a
> where t.tablename = c.columnname and c.columnname = a.alias
> using derby join order (SYSCOLUMNS_HEAP # SYSALIASES_INDEX1) # SYSTABLES_INDEX1
> union all
> select tablename from sys.systables t, sys.syscolumns c, sys.sysaliases a, sys.syssequences
s
> where t.tablename = c.columnname and c.columnname = a.alias and a.alias = s.sequencename
> using derby join order  ((SYSCOLUMNS_HEAP # SYSTABLES_INDEX1) # SYSALIASES_INDEX1) #
SYSSEQUENCES_INDEX2
> ;
> Here's a comparison of these approaches:
> (1)
> + Portability: the same query text can be used against different RDBMSes.
> - Parsing of DERBY-PROPERTIES happens outside the grammer.
> (2)
> + Parsing happens in the parser.
> - Not portable.
> I slightly prefer approach (1). If I pursue that approach, I would like to see if I can
move the parsing into the parser.
> I am interested in other opinions about how to address this feature. Thanks.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message