Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/FastCountResources.sql @ 432:8b7c1c423367 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
author Alain Mazy <am@osimis.io>
date Mon, 11 Dec 2023 14:39:27 +0100
parents cb0aac9bbada
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
79
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
1 -- https://wiki.postgresql.org/wiki/Count_estimate
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
2
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
3 INSERT INTO GlobalIntegers
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
4 SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0; -- Count patients
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
5
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
6 INSERT INTO GlobalIntegers
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
7 SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1; -- Count studies
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
8
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
9 INSERT INTO GlobalIntegers
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
10 SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2; -- Count series
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
11
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
12 INSERT INTO GlobalIntegers
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
13 SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3; -- Count instances
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
14
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
15
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
16 CREATE OR REPLACE FUNCTION CountResourcesTrackerFunc()
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
17 RETURNS TRIGGER AS $$
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
18 BEGIN
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
19 IF TG_OP = 'INSERT' THEN
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
20 UPDATE GlobalIntegers SET value = value + 1 WHERE key = new.resourceType + 2;
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
21 RETURN new;
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
22 ELSIF TG_OP = 'DELETE' THEN
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
23 UPDATE GlobalIntegers SET value = value - 1 WHERE key = old.resourceType + 2;
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
24 RETURN old;
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
25 END IF;
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
26 END;
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
27 $$ LANGUAGE plpgsql;
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
28
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
29
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
30 CREATE TRIGGER CountResourcesTracker
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
31 AFTER INSERT OR DELETE ON Resources
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
32 FOR EACH ROW
cb0aac9bbada optimization for /statistics URI
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
33 EXECUTE PROCEDURE CountResourcesTrackerFunc();