-
-
Save valeneiko/89f8cbe26db7ca2651b47524462b5d18 to your computer and use it in GitHub Desktop.
Introducing a nested query results in optimal query plan
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Query plans: | |
-- * Slow: https://explain.depesz.com/s/joHK | |
-- * Fast: https://explain.depesz.com/s/tgd4 | |
--EXPLAIN (ANALYZE,COSTS, VERBOSE, BUFFERS, FORMAT JSON) | |
WITH cte0 AS | |
( | |
SELECT resource_surrogate_id AS Sid1 | |
FROM fhir.token_search_param | |
WHERE is_history = false | |
AND search_param_id = 600 | |
-- This is Fast | |
--AND system_id IN (SELECT system_id FROM fhir.system WHERE value = 'REDACTED') | |
-- But this is slow | |
AND system_id = 20 | |
AND code_hash IN ('a5b28586-9009-edfc-7b8c-db9d38b6a33b'::uuid) | |
AND (resource_type_id = 10 | |
OR resource_type_id = 52 | |
OR resource_type_id = 95 | |
OR resource_type_id = 119 | |
OR resource_type_id = 60) | |
), | |
cte1 AS | |
( | |
SELECT resource_surrogate_id AS Sid1 | |
FROM fhir.date_time_search_param | |
WHERE is_history = false | |
AND resource_surrogate_id IN (SELECT Sid1 FROM cte0) | |
AND search_param_id = 518 | |
AND start_date_time <= '2022-01-12 12:13:21.969000Z' | |
AND (resource_type_id = 10 | |
OR resource_type_id = 52 | |
OR resource_type_id = 95 | |
OR resource_type_id = 119 | |
OR resource_type_id = 60) | |
), | |
cte2 AS | |
( | |
SELECT refSource.resource_surrogate_id AS Sid2, refTarget.resource_surrogate_id AS Sid1 | |
FROM fhir.reference_search_param refSource | |
INNER JOIN fhir.resource refTarget | |
ON refSource.reference_resource_type_id = refTarget.resource_type_id | |
AND refSource.reference_resource_id_hash = refTarget.resource_id_hash | |
INNER JOIN fhir.resource refSourceResource | |
ON refSource.resource_surrogate_id = refSourceResource.resource_surrogate_id | |
AND refSourceResource.resource_id_hash IN ('df26ca5a-d2e2-1576-2507-815d8e73f15e'::uuid) | |
WHERE refSource.search_param_id = 561 | |
AND refTarget.is_history = false | |
AND refSource.is_history = false | |
AND refSource.resource_type_id IN (42) | |
AND refSource.reference_resource_type_id IN (10, 52, 95, 119, 60) | |
AND refTarget.resource_surrogate_id IN (SELECT Sid1 FROM cte1) | |
AND (refTarget.resource_type_id = 10 | |
OR refTarget.resource_type_id = 52 | |
OR refTarget.resource_type_id = 95 | |
OR refTarget.resource_type_id = 119 | |
OR refTarget.resource_type_id = 60) | |
), | |
cte3 AS | |
( | |
SELECT resource_surrogate_id AS Sid1, start_date_time as sort_value | |
FROM fhir.date_time_search_param | |
WHERE is_history = false | |
AND search_param_id = 518 | |
AND (resource_type_id = 10 | |
OR resource_type_id = 52 | |
OR resource_type_id = 95 | |
OR resource_type_id = 119 | |
OR resource_type_id = 60) | |
AND resource_surrogate_id IN (SELECT Sid1 FROM cte2) | |
), | |
cte4 AS | |
( | |
SELECT DISTINCT Sid1, true AS is_match, false AS is_partial | |
, cte3.sort_value | |
FROM cte3 | |
ORDER BY cte3.sort_value DESC, Sid1 ASC | |
LIMIT 16 | |
) | |
SELECT DISTINCT r.resource_type_id, r.resource_id_encrypted, r.version, r.is_deleted, r.resource_surrogate_id, r.request_method, CAST(is_match AS boolean) AS is_match, CAST(is_partial AS boolean) AS is_partial, r.is_raw_resource_meta_set, r.raw_resource, cte4.sort_value | |
FROM fhir.resource r | |
INNER JOIN cte4 | |
ON r.resource_surrogate_id = cte4.Sid1 | |
ORDER BY cte4.sort_value DESC, r.resource_surrogate_id ASC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Query plan: | |
-- * https://explain.depesz.com/s/vBdG | |
-- EXPLAIN (ANALYZE,COSTS, VERBOSE, BUFFERS, FORMAT JSON) | |
SELECT DISTINCT r.resource_type_id, r.resource_id_encrypted, r.version, r.is_deleted, r.resource_surrogate_id, r.request_method, CAST(true AS boolean) AS is_match, CAST(false AS boolean) AS is_partial, r.is_raw_resource_meta_set, r.raw_resource, cte1.start_date_time as sort_value | |
FROM fhir.resource r | |
INNER JOIN fhir.token_search_param AS cte0 ON | |
cte0.is_history = false | |
AND cte0.search_param_id = 600 | |
AND cte0.system_id = 20 | |
AND cte0.code_hash IN ('a5b28586-9009-edfc-7b8c-db9d38b6a33b'::uuid) | |
AND cte0.resource_type_id = r.resource_type_id | |
AND cte0.resource_surrogate_id = r.resource_surrogate_id | |
INNER JOIN fhir.date_time_search_param AS cte1 ON | |
cte1.is_history = false | |
AND cte1.search_param_id = 518 | |
AND cte1.resource_type_id = r.resource_type_id | |
AND cte1.resource_surrogate_id = r.resource_surrogate_id | |
RIGHT JOIN fhir.reference_search_param AS cte2 ON | |
cte2.is_history = false | |
AND cte2.search_param_id = 561 | |
AND cte2.resource_type_id IN (42) | |
AND cte2.reference_resource_type_id = r.resource_type_id | |
AND cte2.reference_resource_id_hash = r.resource_id_hash | |
INNER JOIN fhir.resource AS cte2Source ON | |
cte2Source.is_history = false | |
AND cte2Source.resource_type_id IN (42) | |
AND cte2Source.resource_surrogate_id = cte2.resource_surrogate_id | |
WHERE cte1.start_date_time <= '2022-01-12 12:13:21.969000Z' | |
AND r.resource_type_id IN (10, 52, 95, 119, 60) | |
AND cte2Source.resource_id_hash IN ('df26ca5a-d2e2-1576-2507-815d8e73f15e'::uuid) | |
ORDER BY cte1.start_date_time DESC, r.resource_surrogate_id ASC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE SCHEMA IF NOT EXISTS fhir; | |
CREATE COLLATION fhir.latin1_general_100_cs_as (LOCALE = 'en-GB', provider = 'icu', deterministic = true); | |
/************************************************************* | |
system table | |
**************************************************************/ | |
CREATE TABLE fhir.system | |
( | |
system_id integer GENERATED ALWAYS AS IDENTITY(START 1 INCREMENT 1) NOT NULL, | |
value varchar(256) NOT NULL | |
); | |
CREATE UNIQUE INDEX IXC_system ON fhir.system | |
( | |
value | |
); | |
ALTER TABLE fhir.system | |
CLUSTER ON IXC_system; | |
/************************************************************* | |
resource table | |
**************************************************************/ | |
CREATE TABLE fhir.resource | |
( | |
resource_type_id smallint NOT NULL, | |
resource_id_hash uuid NOT NULL, | |
resource_id_encrypted bytea NOT NULL, | |
version integer NOT NULL, | |
is_history boolean NOT NULL, | |
resource_surrogate_id bigint NOT NULL, | |
is_deleted boolean NOT NULL, | |
request_method varchar(10) NULL, | |
raw_resource bytea NOT NULL, | |
is_raw_resource_meta_set bit NOT NULL DEFAULT B'0' | |
); | |
CREATE UNIQUE INDEX IXC_resource ON fhir.resource | |
( | |
resource_surrogate_id | |
); | |
ALTER TABLE fhir.resource | |
CLUSTER ON IXC_resource; | |
CREATE UNIQUE INDEX ix_resourceresourcetypeidresourceidversion ON fhir.resource | |
( | |
resource_type_id, | |
resource_id_hash, | |
version | |
); | |
CREATE UNIQUE INDEX ix_resourceresourcetypeidresourceid ON fhir.resource | |
( | |
resource_type_id, | |
resource_id_hash | |
) | |
INCLUDE | |
( | |
version, | |
is_deleted | |
) | |
WHERE is_history = false; | |
CREATE UNIQUE INDEX ix_resourceresourcetypeidresourcesurrgateid ON fhir.resource | |
( | |
resource_type_id, | |
resource_surrogate_id | |
) | |
WHERE is_history = false AND is_deleted = false; | |
CREATE STATISTICS fhir.resource_id_type_deps (dependencies) | |
ON resource_type_id, resource_id_hash | |
FROM fhir.resource; | |
/************************************************************* | |
reference search param | |
**************************************************************/ | |
CREATE TABLE fhir.reference_search_param | |
( | |
resource_type_id smallint NOT NULL, | |
resource_surrogate_id bigint NOT NULL, | |
search_param_id smallint NOT NULL, | |
base_uri varchar(128) COLLATE fhir.latin1_general_100_cs_as NULL, | |
reference_resource_type_id smallint NULL, | |
reference_resource_id_hash uuid NOT NULL, | |
reference_resource_id_encrypted bytea NOT NULL, | |
reference_resource_version integer NULL, | |
is_history boolean NOT NULL | |
); | |
CREATE INDEX IXC_reference_search_param | |
ON fhir.reference_search_param | |
( | |
resource_surrogate_id, | |
search_param_id | |
); | |
ALTER TABLE fhir.reference_search_param | |
CLUSTER ON IXC_reference_search_param; | |
CREATE INDEX ix_rspspirrtirresourceidbaseurireferenceresourceversion | |
ON fhir.reference_search_param | |
( | |
search_param_id, | |
reference_resource_id_hash, | |
reference_resource_type_id, | |
base_uri | |
) | |
INCLUDE | |
( | |
resource_type_id, | |
reference_resource_version | |
) | |
WHERE is_history = false; | |
CREATE STATISTICS fhir.reference_sp_source_deps (dependencies) | |
ON search_param_id, resource_type_id, reference_resource_type_id | |
FROM fhir.reference_search_param; | |
CREATE STATISTICS fhir.reference_source_target_deps (dependencies) | |
ON resource_type_id, reference_resource_type_id | |
FROM fhir.reference_search_param; | |
CREATE STATISTICS fhir.reference_sp_target_mcv (mcv) | |
ON search_param_id, reference_resource_type_id | |
FROM fhir.reference_search_param; | |
/************************************************************* | |
token search param | |
**************************************************************/ | |
CREATE TABLE fhir.token_search_param | |
( | |
resource_type_id smallint NOT NULL, | |
resource_surrogate_id bigint NOT NULL, | |
search_param_id smallint NOT NULL, | |
system_id integer NULL, | |
code_hash uuid NOT NULL, | |
code_encrypted bytea NOT NULL, | |
is_history boolean NOT NULL | |
); | |
CREATE INDEX IXC_token_search_param | |
ON fhir.token_search_param | |
( | |
resource_surrogate_id, | |
search_param_id | |
); | |
ALTER TABLE fhir.token_search_param | |
CLUSTER ON IXC_token_search_param; | |
CREATE INDEX ix_tokenseachparamsearchparamidcodesystemid | |
ON fhir.token_search_param | |
( | |
search_param_id, | |
code_hash, | |
system_id | |
) | |
INCLUDE | |
( | |
resource_type_id | |
) | |
WHERE is_history = false; | |
CREATE STATISTICS fhir.token_code_sp_deps(dependencies) | |
ON search_param_id, code_hash | |
FROM fhir.token_search_param; | |
CREATE STATISTICS fhir.token_code_type_deps(dependencies) | |
ON resource_type_id, code_hash | |
FROM fhir.token_search_param; | |
CREATE STATISTICS fhir.token_code_system_deps(dependencies) | |
ON system_id, code_hash | |
FROM fhir.token_search_param; | |
/************************************************************* | |
date search param | |
**************************************************************/ | |
CREATE TABLE fhir.date_time_search_param | |
( | |
resource_type_id smallint NOT NULL, | |
resource_surrogate_id bigint NOT NULL, | |
search_param_id smallint NOT NULL, | |
start_date_time timestamp(6) NOT NULL, | |
end_date_time timestamp(6) NOT NULL, | |
is_longer_than_a_day boolean NOT NULL, | |
is_history boolean NOT NULL | |
); | |
CREATE INDEX IXC_date_time_search_param | |
ON fhir.date_time_search_param | |
( | |
resource_surrogate_id, | |
search_param_id | |
); | |
ALTER TABLE fhir.date_time_search_param | |
CLUSTER ON IXC_date_time_search_param; | |
CREATE INDEX ix_datetimesearchparamsearchparamidstartdatetimeenddatetime | |
ON fhir.date_time_search_param | |
( | |
search_param_id, | |
start_date_time, | |
end_date_time | |
) | |
INCLUDE | |
( | |
resource_type_id, | |
is_longer_than_a_day | |
) | |
WHERE is_history = false; | |
CREATE INDEX ix_datetimesearchparamsearchparamidenddatetimestartdatetime | |
ON fhir.date_time_search_param | |
( | |
search_param_id, | |
end_date_time, | |
start_date_time | |
) | |
INCLUDE | |
( | |
resource_type_id, | |
is_longer_than_a_day | |
) | |
WHERE is_history = false; | |
CREATE INDEX ix_dtsearchparamsearchparamidstartdatetimeenddatetimelong | |
ON fhir.date_time_search_param | |
( | |
search_param_id, | |
start_date_time, | |
end_date_time | |
) | |
INCLUDE | |
( | |
resource_type_id | |
) | |
WHERE is_history = false AND is_longer_than_a_day = true; | |
CREATE INDEX ix_dtsearchparamsearchparamidenddatetimestartdatetimelong | |
ON fhir.date_time_search_param | |
( | |
search_param_id, | |
end_date_time, | |
start_date_time | |
) | |
INCLUDE | |
( | |
resource_type_id | |
) | |
WHERE is_history = false AND is_longer_than_a_day = true; | |
CREATE STATISTICS fhir.date_sp_type(mcv) | |
ON search_param_id, resource_type_id | |
FROM fhir.date_time_search_param; | |
CREATE STATISTICS fhir.date_start_end(dependencies) | |
ON start_date_time, end_date_time | |
FROM fhir.date_time_search_param; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Statistics