You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Tony Brusseau (Created) (JIRA)" <ji...@apache.org> on 2011/11/24 00:20:39 UTC
[jira] [Created] (DERBY-5513) Derby optimizer doesn't seem to be
doing simplification based on transitivity (at least in certain
circumstantces)
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
[jira] [Updated] (DERBY-5513) Derby optimizer doesn't seem to be
doing simplification based on transitivity (at least in certain
circumstantces)
Posted by "Tony Brusseau (Updated) (JIRA)" <ji...@apache.org>.
[ 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 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.
> 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
[jira] [Updated] (DERBY-5513) Derby optimizer doesn't seem to be
doing simplification based on transitivity (at least in certain
circumstantces)
Posted by "Tony Brusseau (Updated) (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Commented] (DERBY-5513) Derby optimizer doesn't seem to be
doing simplification based on transitivity (at least in certain
circumstantces)
Posted by "Knut Anders Hatlen (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5513?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13156650#comment-13156650 ]
Knut Anders Hatlen commented on DERBY-5513:
-------------------------------------------
I'm not sure if the suggested simplification of the WHERE clause is valid.
If the values are
t1.ARG2 = 0
t2.ITEM_ID = 0
t3.ITEM_ID = 1
(t1.ARG2 = t3.ITEM_ID) AND (t3.ITEM_ID = t2.ITEM_ID) evaluates to FALSE,
whereas (t1.ARG2 = t2.ITEM_ID) evaluates to TRUE, so the two expressions
don't seem to be equivalent.
> 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
[jira] [Commented] (DERBY-5513) Derby optimizer doesn't seem to be
doing simplification based on transitivity (at least in certain
circumstantces)
Posted by "Tony Brusseau (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5513?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13158531#comment-13158531 ]
Tony Brusseau commented on DERBY-5513:
--------------------------------------
You're absolutely right. Closing as invalid, sorry for the inconvenient, I'll go back to the JPA provider which is the source of the silliness.
> 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
[jira] [Closed] (DERBY-5513) Derby optimizer doesn't seem to be
doing simplification based on transitivity (at least in certain
circumstantces)
Posted by "Tony Brusseau (Closed) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5513?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Tony Brusseau closed DERBY-5513.
--------------------------------
Resolution: Invalid
> 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
[jira] [Updated] (DERBY-5513) Derby optimizer doesn't seem to be
doing simplification based on transitivity (at least in certain
circumstantces)
Posted by "Tony Brusseau (Updated) (JIRA)" <ji...@apache.org>.
[ 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
[jira] [Updated] (DERBY-5513) Derby optimizer doesn't seem to be
doing simplification based on transitivity (at least in certain
circumstantces)
Posted by "Tony Brusseau (Updated) (JIRA)" <ji...@apache.org>.
[ 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 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.
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 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.
> 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 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