SQL indices a WC format bump and 1.7

The query STMT_SELECT_NODE_CHILDREN_WALKER_INFO as used in 1.7

   SELECT local_relpath, op_depth, presence, kind
   FROM nodes
   WHERE wc_id = ?1 AND parent_relpath = ?2
   GROUP BY local_relpath
   ORDER BY op_depth DESC

performs poorly and doesn't scale well with working copy size. This
causes recursive functions that use svn_wc__internal_walk_children to be
slow, things like "svn info --depth infinity". I fixed this on trunk by
changing the query and some C code, see r1164426.

On IRC Bert pointed out that we can fix the problem by introducing a new
index:

   NODES(wc_id, parent_relpath, local_relpath, op_depth)

This improves things dramatically. If we add this new index we can
revert r1164426, the index provides a slightly larger performance gain
than the query/C code change.

Bert also suggests changing our other indices by adding wc_id and/or
local_relpath thus allowing them to be UNIQUE. Can anyone confirm that
UNIQUE indices are better?

I think that the I_ROOT and I_LOCAL_ABSPATH indices are unnecessary
given that columns they index are defined as UNIQUE. Can anyone confirm
that we don't need indices on UNIQUE columns?

It's possible that we don't need I_EXTERNALS_PARENT as none of the
queries look like they will use it. Perhaps we should drop it?

So how should we fix the 1.7 performance problem?

- Use r1164426, my non-schema change fix.

- Create a new WC format 30 with the new index.

- Create a new WC format 30 with all the schema changes in the patch
  below.

Changing the WC format would involve auto-upgrading format 29 working
copies. We need to decide whether we want the minimal format 30 change
in 1.7 before we develop this feature on trunk.

Patch to change indices below. This is not a complete patch, it doesn't
bump the format or auto-upgrade. It does pass the regression tests and
improve the performance of recursive info and propset.

Index: subversion/libsvn_wc/wc-metadata.sql
===================================================================
--- subversion/libsvn_wc/wc-metadata.sql (revision 1164459)
+++ subversion/libsvn_wc/wc-metadata.sql (working copy)
@@ -58,7 +58,6 @@
 /* Note: a repository (identified by its UUID) may appear at multiple URLs.
    For example, http://example.com/repos/ and https://example.com/repos/. */
 CREATE INDEX I_UUID ON REPOSITORY (uuid);
-CREATE INDEX I_ROOT ON REPOSITORY (root);
 
 
 /* ------------------------------------------------------------------------- */
@@ -71,7 +70,6 @@
   local_abspath TEXT UNIQUE
   );
 
-CREATE UNIQUE INDEX I_LOCAL_ABSPATH ON WCROOT (local_abspath);
 
 
 /* ------------------------------------------------------------------------- */
@@ -178,8 +176,10 @@
   PRIMARY KEY (wc_id, local_relpath)
   );
 
-CREATE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath);
-CREATE INDEX I_ACTUAL_CHANGELIST ON ACTUAL_NODE (changelist);
+CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath,
+ local_relpath);
+CREATE UNIQUE INDEX I_ACTUAL_CHANGELIST ON ACTUAL_NODE (wc_id, changelist,
+ local_relpath);
 
 
 /* ------------------------------------------------------------------------- */
@@ -478,7 +478,10 @@
 
   );
 
-CREATE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, op_depth);
+CREATE UNIQUE INDEX I_NODES_PARENT1 ON NODES (wc_id, parent_relpath,
+ op_depth, local_relpath);
+CREATE UNIQUE INDEX I_NODES_PARENT2 ON NODES (wc_id, parent_relpath,
+ local_relpath, op_depth);
 
 /* Many queries have to filter the nodes table to pick only that version
    of each node with the highest (most "current") op_depth. This view
@@ -567,7 +570,8 @@
   PRIMARY KEY (wc_id, local_relpath)
 );
 
-CREATE INDEX I_EXTERNALS_PARENT ON EXTERNALS (wc_id, parent_relpath);
+CREATE UNIQUE INDEX I_EXTERNALS_PARENT ON EXTERNALS (wc_id, parent_relpath,
+ local_relpath);
 CREATE UNIQUE INDEX I_EXTERNALS_DEFINED ON EXTERNALS (wc_id,
                                                       def_local_relpath,
                                                       local_relpath);
@@ -804,8 +808,10 @@
   PRIMARY KEY (wc_id, local_relpath)
   );
 
-CREATE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath);
-CREATE INDEX I_ACTUAL_CHANGELIST ON ACTUAL_NODE (changelist);
+CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE (wc_id, parent_relpath,
+ local_relpath);
+CREATE UNIQUE INDEX I_ACTUAL_CHANGELIST ON ACTUAL_NODE (wc_id, changelist,
+ local_relpath);
 
 INSERT INTO ACTUAL_NODE SELECT
   wc_id, local_relpath, parent_relpath, properties, conflict_old,

-- 
uberSVN: Apache Subversion Made Easy
http://www.uberSVN.com