[
https://issues.apache.org/jira/browse/DERBY-5513?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Tony Brusseau updated DERBY-5513:
---------------------------------
Description:
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.
was:
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 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.
> 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 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
