From c7d9b6020f41ef64d9315ec0bf3e2b4a1c59f67b Mon Sep 17 00:00:00 2001 From: Karen Arutyunov Date: Mon, 16 Aug 2021 12:00:39 +0300 Subject: Add selected_package_prerequisites(configuration,prerequisite) index to speed up query_dependents() --- bpkg/database.cxx | 28 ++++++++++++++++++++++++++-- 1 file changed, 26 insertions(+), 2 deletions(-) (limited to 'bpkg/database.cxx') diff --git a/bpkg/database.cxx b/bpkg/database.cxx index 3d83de8..e91e668 100644 --- a/bpkg/database.cxx +++ b/bpkg/database.cxx @@ -55,8 +55,8 @@ namespace bpkg // Register the data migration functions. // - // NOTE: remember to qualify table names with \"main\". if using native - // statements. + // NOTE: remember to qualify table/index names with \"main\". if using + // native statements. // template using migration_entry = odb::data_migration_entry; @@ -87,6 +87,18 @@ namespace bpkg "SET configuration = '" + sl->uuid.string () + "'"); }); + static const migration_entry<10> + migrate_v10 ([] (odb::database& db) + { + // Create the multi-column index for the configuration and prerequisite + // columns of the selected_package_prerequisites table. + // + db.execute ( + "CREATE INDEX " + "\"main\".selected_package_prerequisites_configuration_prerequisite_i " + "ON selected_package_prerequisites (configuration, prerequisite)"); + }); + static inline path cfg_path (const dir_path& d, bool create) { @@ -175,6 +187,18 @@ namespace bpkg schema_catalog::create_schema (*this); + // To speed up the query_dependents() function create the multi- + // column index for the configuration and prerequisite columns of + // the selected_package_prerequisites table. + // + // @@ Use ODB pragma if/when support for container indexes is added. + // + execute ( + "CREATE INDEX " + "selected_package_prerequisites_configuration_prerequisite_i " + "ON selected_package_prerequisites (configuration, " + "prerequisite)"); + persist (*create); // Also assigns link id. // Cache the configuration information. -- cgit v1.1