You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@jackrabbit.apache.org by "Alex Parvulescu (Created) (JIRA)" <ji...@apache.org> on 2012/03/27 18:10:39 UTC
[jira] [Created] (JCR-3280) SQL2 joins on empty sets are not
efficient
SQL2 joins on empty sets are not efficient
------------------------------------------
Key: JCR-3280
URL: https://issues.apache.org/jira/browse/JCR-3280
Project: Jackrabbit Content Repository
Issue Type: Improvement
Reporter: Alex Parvulescu
Assignee: Alex Parvulescu
It seems that in the cases where the LEFT side of the join doesn't contain any hits, the QueryEngine in unable to generate an efficient query for the RIGHT side, so it basically select all the possible nodes.
See this discussion as context [0].
Example:
LEFT side has hits, RIGHT side select is fast given some conditions:
> SQL2 JOIN LEFT SIDE took 18 ms. fetched 145 rows.
> SQL2 JOIN RIGHT SIDE took 67 ms. fetched 0 rows.
LEFT side has no hits, RIGHT select everything
> SQL2 JOIN LEFT SIDE took 8 ms. fetched 0 rows.
> SQL2 JOIN RIGHT SIDE took 845 ms. fetched 13055 rows.
...so it fetches 130k nodes and doesn't keep any of them.
[0] http://jackrabbit.510166.n4.nabble.com/Strange-Search-Performance-problem-with-OR-td4507121.html
--
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] (JCR-3280) SQL2 joins on empty sets are not
efficient
Posted by "Alex Parvulescu (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3280?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13240443#comment-13240443 ]
Alex Parvulescu commented on JCR-3280:
--------------------------------------
cool, thanks Jukka!
> SQL2 joins on empty sets are not efficient
> ------------------------------------------
>
> Key: JCR-3280
> URL: https://issues.apache.org/jira/browse/JCR-3280
> Project: Jackrabbit Content Repository
> Issue Type: Improvement
> Reporter: Alex Parvulescu
> Assignee: Alex Parvulescu
> Fix For: 2.4.1, 2.6
>
> Attachments: JCR-3280.patch
>
>
> It seems that in the cases where the LEFT side of the join doesn't contain any hits, the QueryEngine in unable to generate an efficient query for the RIGHT side, so it basically select all the possible nodes.
> See this discussion as context [0].
> Example:
> LEFT side has hits, RIGHT side select is fast given some conditions:
> > SQL2 JOIN LEFT SIDE took 18 ms. fetched 145 rows.
> > SQL2 JOIN RIGHT SIDE took 67 ms. fetched 0 rows.
> LEFT side has no hits, RIGHT select everything
> > SQL2 JOIN LEFT SIDE took 8 ms. fetched 0 rows.
> > SQL2 JOIN RIGHT SIDE took 845 ms. fetched 13055 rows.
> ...so it fetches 130k nodes and doesn't keep any of them.
> [0] http://jackrabbit.510166.n4.nabble.com/Strange-Search-Performance-problem-with-OR-td4507121.html
--
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] (JCR-3280) SQL2 joins on empty sets are not
efficient
Posted by "Jukka Zitting (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3280?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jukka Zitting updated JCR-3280:
-------------------------------
Fix Version/s: 2.4.1
Nice! This seems important (and low-risk) enough to be included already in 2.4.1, so I merged it to the 2.4 branch in revision 1306367.
> SQL2 joins on empty sets are not efficient
> ------------------------------------------
>
> Key: JCR-3280
> URL: https://issues.apache.org/jira/browse/JCR-3280
> Project: Jackrabbit Content Repository
> Issue Type: Improvement
> Reporter: Alex Parvulescu
> Assignee: Alex Parvulescu
> Fix For: 2.4.1, 2.6
>
> Attachments: JCR-3280.patch
>
>
> It seems that in the cases where the LEFT side of the join doesn't contain any hits, the QueryEngine in unable to generate an efficient query for the RIGHT side, so it basically select all the possible nodes.
> See this discussion as context [0].
> Example:
> LEFT side has hits, RIGHT side select is fast given some conditions:
> > SQL2 JOIN LEFT SIDE took 18 ms. fetched 145 rows.
> > SQL2 JOIN RIGHT SIDE took 67 ms. fetched 0 rows.
> LEFT side has no hits, RIGHT select everything
> > SQL2 JOIN LEFT SIDE took 8 ms. fetched 0 rows.
> > SQL2 JOIN RIGHT SIDE took 845 ms. fetched 13055 rows.
> ...so it fetches 130k nodes and doesn't keep any of them.
> [0] http://jackrabbit.510166.n4.nabble.com/Strange-Search-Performance-problem-with-OR-td4507121.html
--
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] (JCR-3280) SQL2 joins on empty sets are not
efficient
Posted by "Christian Stocker (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3280?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13239717#comment-13239717 ]
Christian Stocker commented on JCR-3280:
----------------------------------------
you're my hero. the queries are now fast.
Will report, if some issues pop up, but testsuite ran without problems
> SQL2 joins on empty sets are not efficient
> ------------------------------------------
>
> Key: JCR-3280
> URL: https://issues.apache.org/jira/browse/JCR-3280
> Project: Jackrabbit Content Repository
> Issue Type: Improvement
> Reporter: Alex Parvulescu
> Assignee: Alex Parvulescu
> Attachments: JCR-3280.patch
>
>
> It seems that in the cases where the LEFT side of the join doesn't contain any hits, the QueryEngine in unable to generate an efficient query for the RIGHT side, so it basically select all the possible nodes.
> See this discussion as context [0].
> Example:
> LEFT side has hits, RIGHT side select is fast given some conditions:
> > SQL2 JOIN LEFT SIDE took 18 ms. fetched 145 rows.
> > SQL2 JOIN RIGHT SIDE took 67 ms. fetched 0 rows.
> LEFT side has no hits, RIGHT select everything
> > SQL2 JOIN LEFT SIDE took 8 ms. fetched 0 rows.
> > SQL2 JOIN RIGHT SIDE took 845 ms. fetched 13055 rows.
> ...so it fetches 130k nodes and doesn't keep any of them.
> [0] http://jackrabbit.510166.n4.nabble.com/Strange-Search-Performance-problem-with-OR-td4507121.html
--
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] (JCR-3280) SQL2 joins on empty sets are not
efficient
Posted by "Jukka Zitting (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3280?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jukka Zitting updated JCR-3280:
-------------------------------
Fix Version/s: (was: 2.6)
2.5
> SQL2 joins on empty sets are not efficient
> ------------------------------------------
>
> Key: JCR-3280
> URL: https://issues.apache.org/jira/browse/JCR-3280
> Project: Jackrabbit Content Repository
> Issue Type: Improvement
> Reporter: Alex Parvulescu
> Assignee: Alex Parvulescu
> Fix For: 2.4.1, 2.5
>
> Attachments: JCR-3280.patch
>
>
> It seems that in the cases where the LEFT side of the join doesn't contain any hits, the QueryEngine in unable to generate an efficient query for the RIGHT side, so it basically select all the possible nodes.
> See this discussion as context [0].
> Example:
> LEFT side has hits, RIGHT side select is fast given some conditions:
> > SQL2 JOIN LEFT SIDE took 18 ms. fetched 145 rows.
> > SQL2 JOIN RIGHT SIDE took 67 ms. fetched 0 rows.
> LEFT side has no hits, RIGHT select everything
> > SQL2 JOIN LEFT SIDE took 8 ms. fetched 0 rows.
> > SQL2 JOIN RIGHT SIDE took 845 ms. fetched 13055 rows.
> ...so it fetches 130k nodes and doesn't keep any of them.
> [0] http://jackrabbit.510166.n4.nabble.com/Strange-Search-Performance-problem-with-OR-td4507121.html
--
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] [Resolved] (JCR-3280) SQL2 joins on empty sets are not
efficient
Posted by "Alex Parvulescu (Resolved) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3280?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Alex Parvulescu resolved JCR-3280.
----------------------------------
Resolution: Fixed
Fix Version/s: 2.6
you are welcome :)
fixed in revision 1306337.
> SQL2 joins on empty sets are not efficient
> ------------------------------------------
>
> Key: JCR-3280
> URL: https://issues.apache.org/jira/browse/JCR-3280
> Project: Jackrabbit Content Repository
> Issue Type: Improvement
> Reporter: Alex Parvulescu
> Assignee: Alex Parvulescu
> Fix For: 2.6
>
> Attachments: JCR-3280.patch
>
>
> It seems that in the cases where the LEFT side of the join doesn't contain any hits, the QueryEngine in unable to generate an efficient query for the RIGHT side, so it basically select all the possible nodes.
> See this discussion as context [0].
> Example:
> LEFT side has hits, RIGHT side select is fast given some conditions:
> > SQL2 JOIN LEFT SIDE took 18 ms. fetched 145 rows.
> > SQL2 JOIN RIGHT SIDE took 67 ms. fetched 0 rows.
> LEFT side has no hits, RIGHT select everything
> > SQL2 JOIN LEFT SIDE took 8 ms. fetched 0 rows.
> > SQL2 JOIN RIGHT SIDE took 845 ms. fetched 13055 rows.
> ...so it fetches 130k nodes and doesn't keep any of them.
> [0] http://jackrabbit.510166.n4.nabble.com/Strange-Search-Performance-problem-with-OR-td4507121.html
--
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] (JCR-3280) SQL2 joins on empty sets are not
efficient
Posted by "Alex Parvulescu (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/JCR-3280?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Alex Parvulescu updated JCR-3280:
---------------------------------
Attachment: JCR-3280.patch
proposed patch.
it seems that if the left side is empty, there is no real need to query the right side anyway.
just for context, right join queries are rewritten to left join before running.
> SQL2 joins on empty sets are not efficient
> ------------------------------------------
>
> Key: JCR-3280
> URL: https://issues.apache.org/jira/browse/JCR-3280
> Project: Jackrabbit Content Repository
> Issue Type: Improvement
> Reporter: Alex Parvulescu
> Assignee: Alex Parvulescu
> Attachments: JCR-3280.patch
>
>
> It seems that in the cases where the LEFT side of the join doesn't contain any hits, the QueryEngine in unable to generate an efficient query for the RIGHT side, so it basically select all the possible nodes.
> See this discussion as context [0].
> Example:
> LEFT side has hits, RIGHT side select is fast given some conditions:
> > SQL2 JOIN LEFT SIDE took 18 ms. fetched 145 rows.
> > SQL2 JOIN RIGHT SIDE took 67 ms. fetched 0 rows.
> LEFT side has no hits, RIGHT select everything
> > SQL2 JOIN LEFT SIDE took 8 ms. fetched 0 rows.
> > SQL2 JOIN RIGHT SIDE took 845 ms. fetched 13055 rows.
> ...so it fetches 130k nodes and doesn't keep any of them.
> [0] http://jackrabbit.510166.n4.nabble.com/Strange-Search-Performance-problem-with-OR-td4507121.html
--
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