Re: svn commit: r1341848 - in /subversion/trunk/subversion: libsvn_wc/wc-queries.sql tests/libsvn_wc/wc-queries-test.c

On Wed, May 23, 2012 at 8:53 AM, <rhuijben_at_apache.org> wrote:
> Author: rhuijben
> Date: Wed May 23 12:53:10 2012
> New Revision: 1341848
>
> URL: http://svn.apache.org/viewvc?rev=1341848&view=rev
> Log:
> Help the Sqlite query planner a bit by rewriting two queries in a way that
> makes it use indexes, where it didn't before.
>
> * subversion/libsvn_wc/wc-queries.sql
>  (STMT_RECURSIVE_UPDATE_NODE_REPO,
>   STMT_SELECT_EXTERNALS_DEFINED): Make the OR operation the outer operation
>     by duplicating some cheap tests.

Hi Bert,

Could you explain in a bit more detail how/why this optimization works?

-- 
Paul T. Burba
CollabNet, Inc. -- www.collab.net -- Enterprise Cloud Development
Skype: ptburba
> * subversion/tests/libsvn_wc/wc-queries-test.c
>  (slow_statements): Remove two slow statements.
>
> Modified:
>    subversion/trunk/subversion/libsvn_wc/wc-queries.sql
>    subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c
>
> Modified: subversion/trunk/subversion/libsvn_wc/wc-queries.sql
> URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_wc/wc-queries.sql?rev=1341848&r1=1341847&r2=1341848&view=diff
> ==============================================================================
> --- subversion/trunk/subversion/libsvn_wc/wc-queries.sql (original)
> +++ subversion/trunk/subversion/libsvn_wc/wc-queries.sql Wed May 23 12:53:10 2012
> @@ -328,10 +328,15 @@ WHERE dav_cache IS NOT NULL AND wc_id =
>
>  -- STMT_RECURSIVE_UPDATE_NODE_REPO
>  UPDATE nodes SET repos_id = ?4, dav_cache = NULL
> -WHERE wc_id = ?1
> -  AND repos_id = ?3
> -  AND (local_relpath = ?2
> -       OR IS_STRICT_DESCENDANT_OF(local_relpath, ?2))
> +/* ### The Sqlite optimizer needs help here ###
> + * WHERE wc_id = ?1
> + *   AND repos_id = ?3
> + *   AND (local_relpath = ?2
> + *        OR IS_STRICT_DESCENDANT_OF(local_relpath, ?2))*/
> +WHERE (wc_id = ?1 AND local_relpath = ?2 AND repos_id = ?3)
> +   OR (wc_id = ?1 AND IS_STRICT_DESCENDANT_OF(local_relpath, ?2)
> +       AND repos_id = ?3)
> +
>
>  -- STMT_UPDATE_LOCK_REPOS_ID
>  UPDATE lock SET repos_id = ?2
> @@ -995,6 +1000,7 @@ SELECT local_relpath, kind, repos_id, de
>  FROM externals
>  LEFT OUTER JOIN repository ON repository.id = externals.repos_id
>  WHERE wc_id = ?1
> +  AND IS_STRICT_DESCENDANT_OF(local_relpath, ?2)
>   AND def_revision IS NULL
>   AND repos_id = (SELECT repos_id FROM nodes
>                   WHERE nodes.local_relpath = ?2)
> @@ -1014,9 +1020,12 @@ WHERE wc_id = ?1
>  -- STMT_SELECT_EXTERNALS_DEFINED
>  SELECT local_relpath, def_local_relpath
>  FROM externals
> -WHERE wc_id = ?1
> -  AND (def_local_relpath = ?2
> -       OR IS_STRICT_DESCENDANT_OF(def_local_relpath, ?2))
> +/* ### The Sqlite optimizer needs help here ###
> + * WHERE wc_id = ?1
> + *   AND (def_local_relpath = ?2
> + *        OR IS_STRICT_DESCENDANT_OF(def_local_relpath, ?2)) */
> +WHERE (wc_id = ?1 AND def_local_relpath = ?2)
> +   OR (wc_id = ?1 AND IS_STRICT_DESCENDANT_OF(def_local_relpath, ?2))
>
>  -- STMT_DELETE_EXTERNAL
>  DELETE FROM externals
>
> Modified: subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c
> URL: http://svn.apache.org/viewvc/subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c?rev=1341848&r1=1341847&r2=1341848&view=diff
> ==============================================================================
> --- subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c (original)
> +++ subversion/trunk/subversion/tests/libsvn_wc/wc-queries-test.c Wed May 23 12:53:10 2012
> @@ -79,7 +79,6 @@ static const int schema_statements[] =
>  static const int slow_statements[] =
>  {
>   /* Operate on the entire WC */
> -  STMT_RECURSIVE_UPDATE_NODE_REPO,
>   STMT_HAS_SWITCHED_WCROOT,
>   STMT_HAS_SWITCHED_WCROOT_REPOS_ROOT,
>   STMT_SELECT_ALL_NODES,
> @@ -93,7 +92,6 @@ static const int slow_statements[] =
>
>   /* Need review: */
>   STMT_SELECT_COMMITTABLE_EXTERNALS_BELOW,
> -  STMT_SELECT_EXTERNALS_DEFINED,
>   STMT_SELECT_EXTERNAL_PROPERTIES,
>   STMT_DELETE_ACTUAL_EMPTIES,