db-derby-dev mailing list archives

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


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 expected.
This query is being generated by JPA so I can't fix it easily by hand (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