db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tony Brusseau (Updated) (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-5513) Derby optimizer doesn't seem to be doing simplification based on transitivity (at least in certain circumstantces)
Date Thu, 24 Nov 2011 00:58:39 GMT

     [ 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

        

Mime
View raw message