From 216b85b053d091306e3311a21f5b050f70a75130 Mon Sep 17 00:00:00 2001 From: Eugen Rochko Date: Mon, 14 Dec 2020 09:06:34 +0100 Subject: Fix performance on instances list in admin UI (#15282) - Reduce duplicate queries - Remove n+1 queries - Add accounts count to detailed view - Add separate action log entry for updating existing domain blocks --- db/views/instances_v01.sql | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) create mode 100644 db/views/instances_v01.sql (limited to 'db/views') diff --git a/db/views/instances_v01.sql b/db/views/instances_v01.sql new file mode 100644 index 000000000..94acd61a1 --- /dev/null +++ b/db/views/instances_v01.sql @@ -0,0 +1,17 @@ +WITH domain_counts(domain, accounts_count) +AS ( + SELECT domain, COUNT(*) as accounts_count + FROM accounts + WHERE domain IS NOT NULL + GROUP BY domain +) +SELECT domain, accounts_count +FROM domain_counts +UNION +SELECT domain_blocks.domain, COALESCE(domain_counts.accounts_count, 0) +FROM domain_blocks +LEFT OUTER JOIN domain_counts ON domain_counts.domain = domain_blocks.domain +UNION +SELECT domain_allows.domain, COALESCE(domain_counts.accounts_count, 0) +FROM domain_allows +LEFT OUTER JOIN domain_counts ON domain_counts.domain = domain_allows.domain -- cgit