stsdatamanagement

STS Data Management Modules, Utilities and Services

To create a database for testing use the following:

docker run --name postgres-5432 -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d --restart unless-stopped postgres
-c max_connections=500
-c shared_buffers=2GB
-c effective_cache_size=6GB
-c maintenance_work_mem=512MB
-c checkpoint_completion_target=0.9
-c wal_buffers=16MB
-c default_statistics_target=100
-c random_page_cost=1.1
-c effective_io_concurrency=200
-c work_mem=1048kB
-c min_wal_size=2GB
-c max_wal_size=8GB
-c max_worker_processes=6
-c max_parallel_workers_per_gather=3
-c max_parallel_workers=6
-c max_parallel_maintenance_workers=3

/* Samples Data {"id":"_b8b74a73_3275_4094_93b1_64a533c5924c_0_0fhir02_0","_resourceType":"Person","name":[{"family":"Mccall","given":"Shane","use":"usual"}],"text":{"div":"Updated record 0","status":"generated"}} */

select count(*) from stsresource;

/* Create General Search Indexes */

/* GIN Index */ --drop index idx_stsresource_gin; CREATE INDEX idx_stsresource_gin ON stsresource USING GIN (resdesc_jsonb);

/* btree indexes */ --drop index idx_stsresource_id; --We don't use where here becuase all FHIR resources will have an id field CREATE INDEX idx_stsresource_id ON stsresource ((resdesc_jsonb->>'id'));

-- Create Indexes for FHIR resources

--DROP INDEX idx_stsresource_given; CREATE INDEX idx_stsresource_given ON stsresource ((resdesc_jsonb->'name'->0->>'given')) WHERE resdesc_jsonb->>'_resourceType' = 'Person';

--drop index idx_stsresource_family; CREATE INDEX idx_stsresource_family ON stsresource ((resdesc_jsonb->'name'->0->>'family')) WHERE resdesc_jsonb->>'_resourceType' = 'Person';

--drop index idx_stsresource_text_div_01; CREATE INDEX idx_stsresource_text_div_01 ON stsresource ((resdesc_jsonb->'text'->>'div')) WHERE resdesc_jsonb->>'_resourceType' = 'Person';

/* btree indexes - alternate method - for queries, quotes will be need '""' */ --drop index idx_stsresource_text_div_02; CREATE INDEX idx_stsresource_text_div_02 ON stsresource ((resdesc_jsonb#>'{text,div}')) WHERE (resdesc_jsonb#>'{_resourceType}')::text = '"Person"';

/* Queries using our indexes */

/* Get records using the GIN index */ SELECT * FROM stsresource WHERE resdesc_jsonb @> '{"_resourceType": "Person"}' AND resdesc_jsonb @> '{"name": [{"family": "Adams"}]}' LIMIT 20

SELECT oid, resdesc_jsonb#>'{text,div}' AS text_div FROM stsresource WHERE resdesc_jsonb @> '{"_resourceType": "Person"}' AND resdesc_jsonb @> '{"text": {"div": "New Record 100"}}';

/* Get record using the BTREE index given */ SELECT resdesc_jsonb->'id' as id, resdesc_jsonb->'name'->0->>'given' as given, resdesc_jsonb->'name'->0->>'family' as family, resdesc_jsonb->'name' as name FROM stsresource WHERE resdesc_jsonb->>'_resourceType' = 'Person' AND resdesc_jsonb->'name'->0->>'given' >= 'y' and resdesc_jsonb->'name'->0->>'given' < 'yzzzzzzzzzzzzz' AND validto is null ORDER BY family, given, id LIMIT 20;

/* Get record using the BTREE index 01 */ SELECT oid, resdesc_jsonb#>'{text,div}' AS text_div FROM stsresource WHERE resdesc_jsonb->>'_resourceType' = 'Person' AND resdesc_jsonb->'text'->>'div' = 'New Record 200';

/* Get record using the BTREE index 02 */ SELECT oid, resdesc_jsonb#>'{text,div}' AS text_div FROM stsresource WHERE (resdesc_jsonb#>'{_resourceType}')::text = '"Person"' AND resdesc_jsonb#>'{text,div}' = '"New Record 400"';

/* Query using id index */ SELECT oid, resdesc_jsonb->>'id' as id, * FROM stsresource WHERE resdesc_jsonb->>'id' >= '_' and resdesc_jsonb->>'id' < '_zzzzzzzzzzzz' ORDER BY resdesc_jsonb->>'id' LIMIT 20;

/* Query using given index */ SELECT distinct given, family FROM ( SELECT resdesc_jsonb->'name'->0->>'given' as given, resdesc_jsonb->'name'->0->>'family' as family FROM stsresource WHERE resdesc_jsonb->>'_resourceType' = 'Person' AND validto is null AND resdesc_jsonb->'name'->0->>'given' >= 'a' AND resdesc_jsonb->'name'->0->>'given' < 'azzzzzzz' ) ORDER BY given DESC LIMIT 20;

/* Query using family indexes */ SELECT distinct resdesc_jsonb->'name'->0->>'given' as given, resdesc_jsonb->'name'->0->>'family' as family FROM stsresource WHERE resdesc_jsonb->>'_resourceType' = 'Person' AND validto is null AND resdesc_jsonb->'name'->0->>'family' >= 'y' AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz' ORDER BY given DESC LIMIT 20

/* Query using family and given indexes */ SELECT distinct givenzz, familyzz FROM ( SELECT resdesc_jsonb->'name'->0->>'given' as givenzz, resdesc_jsonb->'name'->0->>'family' as familyzz FROM stsresource WHERE resdesc_jsonb->>'_resourceType' = 'Person' AND validto is null AND resdesc_jsonb->'name'->0->>'given' >= 'a' AND resdesc_jsonb->'name'->0->>'given' < 'azzzzzzz' AND resdesc_jsonb->'name'->0->>'family' >= 'y' AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz' ) ORDER BY givenzz DESC LIMIT 20;

/* Query using given indexes / select count() from ( SELECT distinct resdesc_jsonb->'name'->0->>'given' as given FROM stsresource WHERE validto is null AND resdesc_jsonb->>'_resourceType' = 'Person' AND resdesc_jsonb->'name'->0->>'given' >= 'al' AND resdesc_jsonb->'name'->0->>'given' < 'alzzzzzzz' )

SELECT distinct resdesc_jsonb->'name'->0->>'given', resdesc_jsonb->'name'->0->>'family' FROM stsresource where validto is null AND resdesc_jsonb->>'_resourceType' = 'Person' AND resdesc_jsonb->'name'->0->>'given' >= 'a' AND resdesc_jsonb->'name'->0->>'given' < 'azzzzzzz' AND resdesc_jsonb->'name'->0->>'family' >= 's' AND resdesc_jsonb->'name'->0->>'family' < 'szzzzzzz' ORDER BY resdesc_jsonb->'name'->0->>'given' DESC, resdesc_jsonb->'name'->0->>'family' DESC LIMIT 20;

SELECT distinct resdesc_jsonb->'name'->0->>'given' as givenzz, resdesc_jsonb->'name'->0->>'family' as familyzz FROM stsresource where validto is null AND resdesc_jsonb->>'_resourceType' = 'Person' AND resdesc_jsonb->'name'->0->>'given' >= 'a' AND resdesc_jsonb->'name'->0->>'given' < 'azzzzzzz' AND resdesc_jsonb->'name'->0->>'family' >= 's' AND resdesc_jsonb->'name'->0->>'family' < 'szzzzzzz' ORDER BY givenzz DESC, familyzz DESC LIMIT 20;

SELECT distinct given as givenzz, family as familyzz FROM stsresource where validto is null AND given >= 'a' AND given < 'azzzzzzz' AND family >= 'n' AND family < 'nzzzzzzz' ORDER BY givenzz DESC, familyzz DESC LIMIT 20;

SELECT given, oid, * FROM stsresource where validto is null and given >= 'a' and given < 'azzzzzz' ORDER BY given LIMIT 20;

select count(*) from stsresource;

SELECT distinct givenzz, familyzz FROM ( SELECT resdesc_jsonb->'name'->0->>'given' as givenzz, resdesc_jsonb->'name'->0->>'family' as familyzz FROM stsresource WHERE resdesc_jsonb->>'_resourceType' = 'Person' AND validto is null AND resdesc_jsonb->'name'->0->>'family' >= 'y' AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz' AND resdesc_jsonb->'name'->0->>'given' >= 'a' AND resdesc_jsonb->'name'->0->>'given' < 'azzzzzzz' ) ORDER BY givenzz DESC LIMIT 20;

SELECT distinct givenzz, familyzz FROM ( select * from ( SELECT resdesc_jsonb->'name'->0->>'given' as givenzz, resdesc_jsonb->'name'->0->>'family' as familyzz FROM stsresource WHERE resdesc_jsonb->>'_resourceType' = 'Person' AND validto is null AND resdesc_jsonb->'name'->0->>'family' >= 'y' AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz' ) where givenzz >= 'a' and givenzz < 'azzzzzzzzzzzz' ) ORDER BY givenzz DESC LIMIT 20;

SELECT * FROM ( SELECT resdesc_jsonb->'name'->0->>'given' AS givenzz, resdesc_jsonb->'name'->0->>'family' AS familyzz FROM stsresource WHERE resdesc_jsonb->>'_resourceType' = 'Person' AND validto IS NULL AND resdesc_jsonb->'name'->0->>'family' >= 'y' AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz' ) subquery WHERE givenzz >= 'a' AND givenzz < 'azzzzzzzzzzzz' ORDER BY givenzz DESC LIMIT 20;

BEGIN;

-- Step 1: Create and populate the temporary table SELECT * INTO TEMPORARY TABLE temp_table_name FROM ( SELECT resdesc_jsonb->'name'->0->>'given' AS givenzz, resdesc_jsonb->'name'->0->>'family' AS familyzz FROM stsresource WHERE resdesc_jsonb->>'_resourceType' = 'Person' AND validto IS NULL AND resdesc_jsonb->'name'->0->>'family' >= 'y' AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz' ) subquery WHERE givenzz >= 'a' AND givenzz < 'azzzzzzzzzzzz';

-- Step 2: Query the temporary table SELECT DISTINCT givenzz, familyzz FROM temp_table_name ORDER BY givenzz DESC LIMIT 20;

DROP TABLE temp_table_name;

COMMIT;

SELECT given, family FROM ( select * from ( SELECT given, family FROM stsresource WHERE validto is null AND given >= 'a' AND given < 'azzzzzzz' ) subquery where family >= 'y' and family < 'yzzzzzzzzzzzz' ) ORDER BY given DESC LIMIT 20;

SELECT given, family FROM ( SELECT given, family FROM stsresource WHERE validto is null AND given >= 'a' AND given < 'azzzzzzz' AND family >= 'y' AND family < 'yzzzzzzzzzzzz' ) ORDER BY given DESC LIMIT 20;

WITH temp_table_name AS ( SELECT resdesc_jsonb->'name'->0->>'given' AS givenzz, resdesc_jsonb->'name'->0->>'family' AS familyzz, oid FROM stsresource WHERE resdesc_jsonb->>'_resourceType' = 'Person' AND validto IS NULL AND resdesc_jsonb->'name'->0->>'family' >= 'y' AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz' ), tt2 AS ( SELECT givenzz, familyzz, oid FROM temp_table_name WHERE givenzz >= 'a' AND givenzz < 'azzzzzzzzzzzz' ) select * from tt2 ORDER BY givenzz ASC LIMIT 20;

-- select * from search_stsresource_given_family_v3('s', 'b'); -- drop function search_stsresource_given_family_v3; CREATE OR REPLACE FUNCTION public.search_stsresource_given_family_v3( _given character varying, _family character varying) RETURNS TABLE (given text, family text, oid bigint) LANGUAGE 'plpgsql' COST 100 VOLATILE ROWS 1000

AS $BODY$ DECLARE _resdesc_jsonb JSONB; BEGIN return query

WITH temp_table_name AS ( SELECT r.resdesc_jsonb->'name'->0->>'given' AS givenzz, r.resdesc_jsonb->'name'->0->>'family' AS familyzz, r.oid FROM stsresource r WHERE r.resdesc_jsonb->>'_resourceType' = 'Person' AND r.validto IS NULL AND r.resdesc_jsonb->'name'->0->>'family' >= _family AND r.resdesc_jsonb->'name'->0->>'family' < _family || 'zzzzzzzzzzzz' ), tt2 AS ( SELECT ttn.givenzz as given, ttn.familyzz as family, ttn.oid FROM temp_table_name ttn WHERE ttn.givenzz >= _given AND ttn.givenzz < _given || 'zzzzzzzzzzzz' ) select * from tt2 ORDER BY given ASC LIMIT 20;

END; $BODY$;

ALTER FUNCTION public.search_stsresource_given_family_v3(character varying, character varying) OWNER TO postgres;

-- select * from search_stsresource_given_family_v1('a', 'y'); -- drop function search_stsresource_given_family_v1; CREATE OR REPLACE FUNCTION public.search_stsresource_given_family_v1( _given character varying, _family character varying) RETURNS TABLE (given character varying, family character varying, oid bigint) LANGUAGE 'plpgsql' COST 100 VOLATILE ROWS 1000

AS $BODY$ DECLARE _resdesc_jsonb JSONB; BEGIN return query

SELECT rt.given, rt.family, rt.oid FROM ( SELECT r.given, r.family, r.oid FROM stsresource r WHERE r.validto is null AND r.given >= _given AND r.given < _given || 'zzzzzzz' AND r.family >= _family AND r.family < _family || 'zzzzzzzzzzzz' ) rt ORDER BY rt.given DESC LIMIT 20;

END; $BODY$;

ALTER FUNCTION public.search_stsresource_given_family_v1(character varying, character varying) OWNER TO postgres;