db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2130) Optimizer performance slowdown from 10.1 to 10.2
Date Fri, 01 Dec 2006 17:41:21 GMT
    [ http://issues.apache.org/jira/browse/DERBY-2130?page=comments#action_12454963 ] 
A B commented on DERBY-2130:

Just a random though that occured to me...

The query in question is written as:

prepare p1 as '
select count(distinct t1.c5) 
    from table19 t1 
        inner join table05 t3 on ( t1.c5 = t3.c2)  
        inner join table06 t4 on ( t3.c33 = t4.c1)  
        inner join v3 t5 on ( t4.c34 = t5.c1)  
        inner join table20 t2 on ( t5.c3 = t2.c5)  
        inner join v1 t6 on ( t2.c35 = t6.c1)  
        inner join v2 t7 on ( t6.c3 = t7.c5)  
        inner join v1 t8 on ( t7.c22 = t8.c1)  
    where (((t8.c3 = ? or t8.c3 = ? or t8.c3 = ? or t8.c3 = ?)))';

Is the use of the explicit "inner join" syntax an intentional decision?  Or more specifically,
is the intention that the tables are to be joined in a specific order?  From the Optimizer
standpoint this query  becomes one large outer query with 8 Optimizables in the from list,
so the Optimizer will attempt to evaluate all combinations of the 8 Optimizables before quitting--which
takes a (long) while.  If, however, the query was written with a specific join order in mind,
you could use Derby optimizer overrides to fix the join order of the 8 Optimizables to be
the join order in which they are specified:

prepare p1 as '
select count(distinct t1.c5) 
    from  --DERBY-PROPERTIES joinOrder=FIXED
            table19 t1 
            inner join table05 t3 on ( t1.c5 = t3.c2)  
            inner join table06 t4 on ( t3.c33 = t4.c1)  
            inner join v3 t5 on ( t4.c34 = t5.c1)  
            inner join table20 t2 on ( t5.c3 = t2.c5)  
            inner join v1 t6 on ( t2.c35 = t6.c1)  
            inner join v2 t7 on ( t6.c3 = t7.c5)  
            inner join v1 t8 on ( t7.c22 = t8.c1)  
    where (((t8.c3 = ? or t8.c3 = ? or t8.c3 = ? or t8.c3 = ?)))';

When I did this the "prepare' finished in about 3 seconds on the same Red Hat Linux machine
with the latest trunk branch (sane build).

Of course, I still think this issue is one that needs to be resolved.  But if a) you're looking
for a temprorary workaround and b) you know the order in which you would like the tables to
be joined, maybe the optimizer override can help in the short term.

Please note, though, that Derby optimization as a whole would benefit a *lot* more if you
continued to help resolve this problem :)

> Optimizer performance slowdown from 10.1 to 10.2
> ------------------------------------------------
>                 Key: DERBY-2130
>                 URL: http://issues.apache.org/jira/browse/DERBY-2130
>             Project: Derby
>          Issue Type: Bug
>          Components: Performance, SQL
>    Affects Versions:,,
>            Reporter: Bryan Pendleton
>         Attachments: repro.sql
> Attached is 'repro.sql', an IJ script which demonstrates what I
> believe to be a serious performance issue in the Optimizer.
> I have run this script in a number of configurations:
>  - the script runs successfully. The 'prepare' statement
>    takes about 90 seconds, on a fairly powerful Windows machine
>  - the script produces a NPE. I believe this is DERBY-1777
>  - the script runs successfully. The 'prepare' statement
>    often takes about 220 seconds, on the same Windows machine
>    Intermittently, on 10.2 and on the trunk, the prepare statement takes
>    15+ minutes. I cannot reliably reproduce this; I run the same script
>    several times in a row and I cannot predict whether it will take 220
>    seconds or whether it will take 15+ minutes.
> I am quite motivated to work on this problem, as this is blocking me from
> using Derby for a project that I'm quite keen on, but I need some
> suggestions and ideas about how to attack it. From my perspective
> there are 3 primary topics:
> 1) Why did optimizer performance for this query degrade so significantly
> from to 10.2? The optimizer seems to be at least 2.5 times slower,
> for this particular query at least, in 10.2. Sometimes it is 10x slower.
> 2) What is the source of the non-determinism? Why does the optimizer
> often take 4 minutes to optimize this query on the trunk, but sometimes
> take 15+ minutes? I don't believe that I'm changing anything from
> run to run.
> 3) Can we improve the optimizer performance even beyond what it was
> for 10.1.2? I realize that this is an ugly query, but I was hoping to
> see an optimization time of 5-10 seconds, not 90 seconds (and certainly
> not 220 seconds).
> I have attempted to start answering some of these questions, with
> limited success. Here is some of what I think I've discovered so far:
>  - the optimizer changes in 10.2 seem to have given the optimizer many
>    more choices of possible query plans to consider. I think this means
>    that, if the optimizer does not time out, it will spend substantially
>    more time optimizing because there are more choices to evaluate. Does
>    this by itself mean that the optimizer will take 2.5 times longer in
>    10.2 than it did in 10.1?
>  - something about this query seems to make the costing mechanism go
>    haywire, and produce extreme costs. While stepping through the
>    optimization of this query in the debugger I have seen it compute
>    costs like 1e63 and 1e200. This might be very closely related to
>    DERBY-1905, although I don't think I'm doing any subqueries here.
>    But maybe I'm misunderstanding the term "subquery" in DERBY-1905.
>    At any rate, due to the enormous estimated costs, timeout does not
>    occur.
>  - the WHERE clause in this query is converted during compilation to 
>    an equivalent IN clause, I believe, which then causes me to run into
>    a number of the problems described in DERBY-47 and DERBY-713.
>    Specifically, rather than constructing a plan which involves 4
>    index probes for the 4 WHERE clause values, the optimizer decides
>    that an index scan must be performed and that it will have to process
>    the entire index (because the query uses parameter markers, not
>    literal values). So perhaps solving DERBY-47 would help me
>  - the optimizer in fact comes up with a "decent" query plan quite quickly.
>    I have experimented with placing a hard limit into the optimizer
>    timeout code, so that I can force optimization to stop after an
>    arbitrary fixed period of time. Then I have been able to set that
>    value to as low as 1 second, and the optimizer has produced plans
>    that then execute in a few milliseconds. Of course, I have only tried
>    this with a trivial amount of data in my database, so it's possible
>    that the plan produced by the optimizer after just a second of
>    optimizing is in fact poor, and I'm just not noticing it because my
>    data sizes are so small.
> At this point, what would be really helpful to me would be some suggestions
> about some general approaches or techniques to try to start breaking down
> and analyzing this problem.

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message