Introducing a nested query results in optimal query plan

Query.sql

-- 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

QueryOptimized.sql

-- 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

Schema.sql

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;

Settings

name	current_setting	source
application_name	pgAdmin 4 - CONN:5258987	session
archive_command	envdir ""/run/etc/wal-e.d/env"" wal-g wal-push ""%p""	configuration file
archive_mode	on	configuration file
archive_timeout	30min	configuration file
autovacuum_analyze_scale_factor	0.02	configuration file
autovacuum_max_workers	5	configuration file
autovacuum_vacuum_scale_factor	0.05	configuration file
bg_mon.history_buckets	120	configuration file
bg_mon.listen_address	::	configuration file
checkpoint_completion_target	0.9	configuration file
checkpoint_timeout	30min	configuration file
client_encoding	UNICODE	session
cluster_name	minder-prod-postgres	command line
DateStyle	ISO, MDY	configuration file
default_text_search_config	pg_catalog.english	configuration file
dynamic_shared_memory_type	posix	configuration file
effective_cache_size	8511MB	configuration file
extwlist.custom_path	/scripts	configuration file
extwlist.extensions	btree_gin,btree_gist,citext,hstore,intarray,ltree,pgcrypto,pgq,pg_trgm,postgres_fdw,tablefunc,uuid-ossp,hypopg,timescaledb,pg_partman	configuration file
hot_standby	on	command line
idle_in_transaction_session_timeout	5s	configuration file
lc_messages	en_US.utf-8	configuration file
lc_monetary	en_US.utf-8	configuration file
lc_numeric	en_US.utf-8	configuration file
lc_time	en_US.utf-8	configuration file
listen_addresses	*	command line
log_autovacuum_min_duration	0	configuration file
log_checkpoints	on	configuration file
log_connections	on	configuration file
log_destination	csvlog	configuration file
log_directory	/pgtemp/logs	configuration file
log_disconnections	on	configuration file
log_file_mode	0644	configuration file
log_filename	postgresql-%u.log	configuration file
log_line_prefix	%t [%p]: [%l-1] %c %x %d %u %a %h	configuration file
log_lock_waits	on	configuration file
log_min_duration_statement	500ms	configuration file
log_rotation_age	1d	configuration file
log_statement	ddl	configuration file
log_temp_files	0	configuration file
log_timezone	Etc/UTC	configuration file
log_truncate_on_rotation	on	configuration file
logging_collector	on	configuration file
max_connections	370	command line
max_locks_per_transaction	128	command line
max_prepared_transactions	0	command line
max_replication_slots	10	command line
max_stack_depth	2MB	environment variable
max_wal_senders	10	command line
max_wal_size	2GB	configuration file
max_worker_processes	8	command line
min_wal_size	80MB	configuration file
pg_stat_statements.track_utility	off	configuration file
port	5432	command line
recovery_target		configuration file
recovery_target_lsn		configuration file
recovery_target_name		configuration file
recovery_target_time		configuration file
recovery_target_timeline	latest	configuration file
recovery_target_xid		configuration file
restore_command	envdir ""/run/etc/wal-e.d/env"" /scripts/restore_command.sh ""%f"" ""%p""	configuration file
shared_buffers	5674MB	configuration file
shared_preload_libraries	bg_mon,pg_stat_statements,pgextwlist,pg_auth_mon,set_user,timescaledb,pg_cron,pg_stat_kcache	configuration file
ssl	on	configuration file
ssl_cert_file	/run/certs/server.crt	configuration file
ssl_key_file	/run/certs/server.key	configuration file
statement_timeout	30s	configuration file
stats_temp_directory	/pgtemp/pg_stat_tmp	configuration file
tcp_keepalives_idle	900	configuration file
tcp_keepalives_interval	100	configuration file
temp_tablespaces	ephemeral	configuration file
TimeZone	Etc/UTC	configuration file
track_commit_timestamp	off	command line
track_functions	all	configuration file
wal_keep_size	128MB	configuration file
wal_level	replica	command line
wal_log_hints	on	command line

Table Size

relname	relpages	reltuples	relallvisible	relkind	relnatts	relhassubclass	reloptions	pg_table_size
reference_search_param	1309143	52626064.0	801163	r	9	False	NULL	10927775744
date_time_search_param	372340	38393380.0	253807	r	7	False	NULL	3095011328
resource	2072891	19308012.0	1448175	r	10	False	NULL	17160003584
token_search_param	3002917	138322770.0	2056097	r	7	False	NULL	24852553728

Statistics

frac_mcv	tablename	attname	inherited	null_frac	n_distinct	n_mcv	n_hist	correlation
NULL	date_time_search_param	end_date_time	False	0	1645961	NULL	101	0.7935925
1	date_time_search_param	is_history	False	0	2	2	NULL	-0.04295493
1	date_time_search_param	is_longer_than_a_day	False	0	1	1	NULL	1
NULL	date_time_search_param	resource_surrogate_id	False	0	2973963	NULL	101	0.85228145
0.99953336	date_time_search_param	resource_type_id	False	0	8	3	5	0.99700856
0.99986666	date_time_search_param	search_param_id	False	0	8	5	3	0.50340044
NULL	date_time_search_param	start_date_time	False	0	1658355	NULL	101	0.79405445
NULL	reference_search_param	base_uri	False	1	0	NULL	NULL	NULL
1	reference_search_param	is_history	False	0	2	2	NULL	0.07547684
NULL	reference_search_param	reference_resource_id_encrypted	False	0	-1	NULL	101	-0.43543392
0.36879998	reference_search_param	reference_resource_id_hash	False	0	1595	100	101	-0.020704137
1.0000001	reference_search_param	reference_resource_type_id	False	0	10	10	NULL	0.19250163
NULL	reference_search_param	reference_resource_version	False	1	0	NULL	NULL	NULL
NULL	reference_search_param	resource_surrogate_id	False	0	611233	NULL	101	0.8652242
0.99999994	reference_search_param	resource_type_id	False	0	13	13	NULL	0.9887591
0.9984	reference_search_param	search_param_id	False	0	33	13	20	0.45191228
1	resource	is_deleted	False	0	1	1	NULL	1
1	resource	is_history	False	0	2	2	NULL	0.026870526
1	resource	is_raw_resource_meta_set	False	0	2	2	NULL	0.6599119
NULL	resource	raw_resource	False	0	-1	NULL	101	-0.4657757
1	resource	request_method	False	0	2	2	NULL	0.41184518
NULL	resource	resource_id_encrypted	False	0	-1	NULL	101	-0.4664205
0.0006	resource	resource_id_hash	False	0	945716	2	101	-0.0043938993
NULL	resource	resource_surrogate_id	False	0	-1	NULL	101	0.7971699
0.9986334	resource	resource_type_id	False	0	15	4	11	0.9924031
0.99806666	resource	version	False	0	58	3	55	0.41487777
NULL	token_search_param	code_encrypted	False	0	-1	NULL	101	-0.44334108
0.8622999	token_search_param	code_hash	False	0	4668	58	101	0.054307997
1	token_search_param	is_history	False	0	2	2	NULL	0.05344057
NULL	token_search_param	resource_surrogate_id	False	0	254926	NULL	101	0.78165466
0.9996334	token_search_param	resource_type_id	False	0	7	3	4	0.9982591
0.9983333	token_search_param	search_param_id	False	0	20	9	11	0.2198201
0.9986334	token_search_param	system_id	False	0.00006666667	30	15	15	0.21969168