From e39c9da028a9a29a0212cdb0287b0046bb786c32 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Thu, 9 Mar 2023 08:29:39 +0000 Subject: Store the distribution of derivations related to packages MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This might be generally useful, but I've been looking at it as it offers a way to try and improve query performance when you want to select all the derivations related to the packages for a revision. The data looks like this (for a specified system and target): ┌───────┬───────┐ │ level │ count │ ├───────┼───────┤ │ 15 │ 2 │ │ 14 │ 3 │ │ 13 │ 3 │ │ 12 │ 3 │ │ 11 │ 14 │ │ 10 │ 25 │ │ 9 │ 44 │ │ 8 │ 91 │ │ 7 │ 1084 │ │ 6 │ 311 │ │ 5 │ 432 │ │ 4 │ 515 │ │ 3 │ 548 │ │ 2 │ 2201 │ │ 1 │ 21162 │ │ 0 │ 22310 │ └───────┴───────┘ Level 0 reflects the number of packages. Level 1 is similar as you have all the derivations for the package origins. The remaining levels contain less packages since it's mostly just derivations involved in bootstrapping. When using a recursive CTE to collect all the derivations, PostgreSQL assumes that the each derivation has the same number of inputs, and this leads to a large overestimation of the number of derivations per a revision. This in turn can lead to PostgreSQL picking a slower way of running the query. When it's known how many new derivations you should see at each level, it's possible to inform PostgreSQL this by using LIMIT's at various points in the query. This reassures the query planner that it's not going to be handling lots of rows and helps it make better decisions about how to execute the query. --- ...guix_revision_package_derivation_distribution_counts.sql | 13 +++++++++++++ 1 file changed, 13 insertions(+) create mode 100644 sqitch/deploy/guix_revision_package_derivation_distribution_counts.sql (limited to 'sqitch/deploy') diff --git a/sqitch/deploy/guix_revision_package_derivation_distribution_counts.sql b/sqitch/deploy/guix_revision_package_derivation_distribution_counts.sql new file mode 100644 index 0000000..58829c5 --- /dev/null +++ b/sqitch/deploy/guix_revision_package_derivation_distribution_counts.sql @@ -0,0 +1,13 @@ +-- Deploy guix-data-service:guix_revision_package_derivation_distribution_counts to pg + +BEGIN; + +CREATE TABLE guix_revision_package_derivation_distribution_counts ( + guix_revision_id integer NOT NULL REFERENCES guix_revisions (id), + system_id integer NOT NULL REFERENCES systems (id), + target varchar NOT NULL, + level integer NOT NULL, + distinct_derivations integer NOT NULL +); + +COMMIT; -- cgit v1.2.3