Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 24 Nov 2011 00:58:39 +0000 (UTC)
From: "Tony Brusseau (Updated) (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <815466482.8182.1322096319897.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <553454475.7764.1322090439956.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Updated] (DERBY-5513) Derby optimizer doesn't seem to be
 doing simplification based on transitivity (at least in certain
 circumstantces)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-5513?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Tony Brusseau updated DERBY-5513:
---------------------------------

    Attachment: query-plan-unlimited.log
                query-plan-limited.log

Limitted and unlimmeted query plans.
                
> Derby optimizer doesn't seem to be doing simplification based on transitivity (at least in certain circumstantces)
> ------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5513
>                 URL: https://issues.apache.org/jira/browse/DERBY-5513
>             Project: Derby
>          Issue Type: Improvement
>          Components: Store
>    Affects Versions: 10.8.1.2
>         Environment: OpenSuse 11.4
>            Reporter: Tony Brusseau
>         Attachments: query-plan-limited.log, query-plan-unlimited.log
>
>
> I believe the following query which has a superfluous join on KB.ITEM t3 should be optimized away, but isn't since queries are running about 5 orders of magnitude slower than when I remove the redundant join. This query is being generated by JPA so I can't fix it in production (I've opened a bug with the JPA provider as well).
> SELECT DISTINCT t0.TERM_ID AS a1, t0.ASSERTED_ARGUMENT AS a2,
> t0.ASSERTED_TIMESTAMP AS a3, t0.DIRECTION AS a4, t0.STRENGTH AS a5, t0.TRUTH AS
> a6, t0.ASSERTED_BY AS a7, t0.ASSERTED_REASON AS a8, t0.FORMULA_ID AS a9,
> t0.MICROTHEORY_ID AS a10 
> FROM KB.GAF_ASSERTION_TERM t0, KB.FORMULA_TERM t1 
> WHERE ((((t0.FORMULA_ID = t1.TERM_ID) AND (t1.ARG0 = 1407374883554030)) AND
> (t1.FORMULA_TYPE = 1)) AND 
> EXISTS (SELECT 1 
> FROM KB.ITEM t3, KB.FORMULA_ENTRIES t2 
> WHERE (((t1.ARG2 = t3.ITEM_ID) AND (t2.ARG_0_TERM = 1407374883557531)) AND
> (t3.ITEM_ID = t2.ITEM_ID))) )
> Namely:
>   (t1.ARG2 = t3.ITEM_ID) AND (t3.ITEM_ID = t2.ITEM_ID)
> Via general transitivity can be re-written as:
>   (t1.ARG2 = t2.ITEM_ID) AND (t3.ITEM_ID =  t3.ITEM_ID)
> The tautology, (t3.ITEM_ID =  t3.ITEM_ID), can be safely deleted.
> Yeilding just:
>   (t1.ARG2 = t2.ITEM_ID)
> Since the WHERE clause in the exist join no long mentions t3, the join on KB.ITEM t3
> can be completely removed.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira