For reference, clean load of the page for this card in GATCG index takes roughly 1s for all elements to be rendered, cached load takes a similar amount.
EXPLAIN WITH RECURSIVE
cte1(top_level_collection_pk, collection_pk, related_collection_pk, index, level)
AS (SELECT c.pk AS top_level_collection_pk,
c.pk as collection_pk,
c.pk AS related_collection_pk,
0 as index,
0 as level
FROM collection AS c
WHERE c.pk = ANY
('{"961d4453-2d9d-3d07-b1c3-1a5202fc1526","af278b9a-487c-3b16-be8e-2eeaf96681f6","a59b7797-18c8-3a07-801d-550a2f624528","0a33d932-28c8-325d-b978-8ca8f62e53fa","a37db02d-198f-35b1-960e-155743410e01","7cec4bd1-5c49-3ec8-b273-a0405ad13622","3b3eff23-8d95-3c4f-a4dd-c233374b201b","dc74d0ed-0b27-33dc-8ba9-cee32988fcfe","2b628ae7-c61e-34c9-8742-6ed8f124f508","a8cd41ca-5518-3aee-bb39-adac9b5438d9","f1c1b09e-95d3-3bf4-9bb6-f1fced2f800e","045e1932-f2ce-3403-b83b-3cf3ad7ec529","83fc16de-0014-3ff4-8aab-a837466047b8","0bf302ad-214d-391e-a259-1e4c1384beaf","d4b91d84-4ce3-303b-a75a-297dd5e50b16","c83b71da-7bf4-3351-87f6-b6d68d72753d","d8556c10-514a-329d-be73-ae212fefcddd","ce496847-d81c-34df-9b23-832e521189ce","876aff4b-b1e9-379b-9062-32f79247a008","91b7bcee-78c9-322c-9695-bbea31b0c7ea","a007de89-09ee-3547-9074-779ca34b6ffa","75c1bdc8-45c7-3cb5-8130-82eefae797e6","cc4e07c9-8dda-3846-b759-5a71e703d7af","eae9b3e2-bfdc-36e2-bb8d-bf064b11e4bc","874b1847-891b-35ad-b4da-b78f633fd214","5d0a2d46-54e3-3a59-a7c3-88b69b90844c","c39928fb-ef6c-32a2-afed-06bfd3d1b49f","08e7a7cf-fd5a-35f0-b8b0-c86c1055fd97","2234978b-eb88-37b9-be92-7f8fad47ffa0","d1486d3c-86bb-3c4d-ad53-8481b8fdf464","3642a132-2fc1-34ab-a8fe-b99f648e98dd","87b56234-a9b1-354c-9575-33ebe14cfd0c","f403d55d-5eb0-3bd8-bed4-f957437fb150","01b52df1-d622-3462-947d-8558a9108719","a029cbf2-6381-3fc0-b045-24f4452f4ca2","033bd96a-5a27-3a4f-8c16-36fff47eb077","53934f63-69bd-3df7-ba11-abdeb27784a2","5dd44efd-5a97-354f-9727-d2f093ef0177","60e3b277-1da2-3242-8363-f0c7909a675d"}'::uuid[])
UNION
SELECT cte1.top_level_collection_pk, r.collection_pk, r.related_collection_pk, r.index, cte1.level + 1
FROM cte1
INNER JOIN relationship AS r ON cte1.related_collection_pk = r.collection_pk WHERE r.relationship_type = 'SourceOfPropertiesAndPropertyValues')
/*
CTE1 execution times:
0224 ms
0277 ms
0369 ms
0355 ms
0322 ms
*/
,cte2(distinct_collection_pk)
AS (SELECT DISTINCT ON (distinct_collection_pk) coalesce(cte1.related_collection_pk, cte1.top_level_collection_pk) AS distinct_collection_pk
FROM cte1)
/*
CTE2 execution times (includes CTE1):
0133 ms
0265 ms
0263 ms
0146 ms
0326 ms
*/
,cte3(distinct_collection_pks) AS (SELECT array_agg(cte2.distinct_collection_pk) AS distiction_collection_pks
FROM cte2)
/*
CTE3 execution times (includes CTE2):
0377 ms
0199 ms
0266 ms
0216 ms
0272 ms
*/
,cte4(distinct_property_pk) AS (SELECT distinct on (pc.property_pk) pc.property_pk
FROM property_collection AS pc
INNER JOIN cte2 ON cte2.distinct_collection_pk = pc.collection_pk)
/*
CTE4 execution times (includes CTE2):
0294 ms
0272 ms
0174 ms
0316 ms
0116 ms
*/
,cte5(distinct_property_pks) AS (SELECT array_agg(cte4.distinct_property_pk) AS distinct_property_pks FROM cte4)
/*
CTE5 execution times (includes CTE4, CTE2):
0114 ms
0257 ms
0274 ms
0103 ms
0270 ms
*/
,cte6(top_level_collection_pk, related_collection_pk, property_pk) AS (SELECT top_level_collection_pk,
array_remove(array_agg(related_collection_pk), NULL) as related_collection_pk,
property_pk
FROM cte1
LEFT JOIN property_collection AS pc
ON (cte1.top_level_collection_pk =
pc.collection_pk OR
pc.collection_pk =
cte1.related_collection_pk)
WHERE pc.property_pk IS NOT NULL
GROUP BY top_level_collection_pk, property_pk)
/*
CTE6 execution times (includes CTE1):
0582 ms
0792 ms
0699 ms
0788 ms
0700 ms
*/
,cte7(pk, collection_pk, property_pk, index, property_value_text, property_value_bytes, property_value_smallint,
property_value_int, property_value_bigint, property_value_numeric, property_value_float, property_value_double,
property_value_boolean, property_value_date, property_value_time, property_value_timestamp,
property_value_uuid, property_value_json) AS (SELECT pv.pk,
pv.collection_pk,
pv.property_pk,
pv.index,
pv.property_value::text AS property_value_text,
NULL::bytea AS property_value_bytes,
NULL::smallint AS property_value_smallint,
NULL::int AS property_value_int,
NULL::bigint AS property_value_bigint,
NULL::numeric AS property_value_numeric,
NULL::real AS property_value_float,
NULL::double precision AS property_value_double,
NULL::boolean AS property_value_boolean,
NULL::date AS property_value_date,
NULL::time AS property_value_time,
NULL::timestamp AS property_value_timestamp,
NULL
::uuid AS property_value_uuid,
NULL::jsonb AS property_value_json
FROM property_value_text AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk,
pv.collection_pk,
pv.property_pk,
pv.index,
NULL::text AS property_value_text,
pv.property_value::bytea AS property_value_bytes,
NULL::smallint AS property_value_smallint,
NULL::int AS property_value_int,
NULL::bigint AS property_value_bigint,
NULL::numeric AS property_value_numeric,
NULL::real AS property_value_float,
NULL::double precision AS property_value_double,
NULL::boolean AS property_value_boolean,
NULL::date AS property_value_date,
NULL::time AS property_value_time,
NULL::timestamp AS property_value_timestamp,
NULL::uuid AS property_value_uuid,
NULL::jsonb AS property_value_json
FROM property_value_bytes AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk,
pv.collection_pk,
pv.property_pk,
pv.index,
NULL::text AS property_value_text,
NULL::bytea AS property_value_bytes,
pv.property_value::smallint AS property_value_smallint,
NULL::int AS property_value_int,
NULL::bigint AS property_value_bigint,
NULL::numeric AS property_value_numeric,
NULL::real AS property_value_float,
NULL::double precision AS property_value_double,
NULL::boolean AS property_value_boolean,
NULL::date AS property_value_date,
NULL::time AS property_value_time,
NULL::timestamp AS property_value_timestamp,
NULL::uuid AS property_value_uuid,
NULL::jsonb AS property_value_json
FROM property_value_smallint AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk
, pv.collection_pk
, pv.property_pk
, pv.index
, NULL::text AS property_value_text
, NULL::bytea AS property_value_bytes
, NULL::smallint AS property_value_smallint, pv.property_value::int AS property_value_int
, NULL::bigint AS property_value_bigint
, NULL::numeric AS property_value_numeric
, NULL::real AS property_value_float
, NULL::double precision AS property_value_double
, NULL::boolean AS property_value_boolean
, NULL::date AS property_value_date
, NULL::time AS property_value_time
, NULL::timestamp AS property_value_timestamp
, NULL::uuid AS property_value_uuid
, NULL::jsonb AS property_value_json
FROM property_value_int AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk,
pv.collection_pk,
pv.property_pk,
pv.index,
NULL::text AS property_value_text,
NULL::bytea AS property_value_bytes,
NULL::smallint AS property_value_smallint,
NULL::int AS property_value_int,
pv.property_value::bigint AS property_value_bigint,
NULL::numeric AS property_value_numeric,
NULL::real AS property_value_float,
NULL::double precision AS property_value_double,
NULL::boolean AS property_value_boolean,
NULL::date AS property_value_date,
NULL::time AS property_value_time,
NULL::timestamp AS property_value_timestamp,
NULL::uuid AS property_value_uuid,
NULL::jsonb AS property_value_json
FROM property_value_bigint AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk
, pv.collection_pk
, pv.property_pk
, pv.index
, NULL::text AS property_value_text
, NULL::bytea AS property_value_bytes
, NULL::smallint AS property_value_smallint
, NULL::int AS property_value_int
, NULL::bigint AS property_value_bigint
, pv.property_value::numeric AS property_value_numeric
, NULL::real AS property_value_float
, NULL::double precision AS property_value_double
, NULL::boolean AS property_value_boolean
, NULL::date AS property_value_date
, NULL::time AS property_value_time
, NULL::timestamp AS property_value_timestamp
, NULL::uuid AS property_value_uuid
, NULL::jsonb AS property_value_json
FROM property_value_numeric AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk,
pv.collection_pk,
pv.property_pk,
pv.index,
NULL::text AS property_value_text,
NULL::bytea AS property_value_bytes,
NULL::smallint AS property_value_smallint,
NULL::int AS property_value_int,
NULL::bigint AS property_value_bigint,
NULL::numeric AS property_value_numeric,
pv.property_value::real AS property_value_float,
NULL::double precision AS property_value_double,
NULL::boolean AS property_value_boolean,
NULL::date AS property_value_date,
NULL::time AS property_value_time,
NULL::timestamp AS property_value_timestamp,
NULL::uuid AS property_value_uuid,
NULL::jsonb AS property_value_json
FROM property_value_float AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk,
pv.collection_pk,
pv.property_pk,
pv.index,
NULL::text AS property_value_text,
NULL::bytea AS property_value_bytes,
NULL::smallint AS property_value_smallint,
NULL::int AS property_value_int,
NULL::bigint AS property_value_bigint,
NULL::numeric AS property_value_numeric,
NULL::real AS property_value_float,
pv.property_value::double precision AS property_value_double,
NULL::boolean AS property_value_boolean,
NULL::date AS property_value_date,
NULL::time AS property_value_time,
NULL::timestamp AS property_value_timestamp,
NULL::uuid AS property_value_uuid,
NULL::jsonb AS property_value_json
FROM property_value_double AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk
, pv.collection_pk
, pv.property_pk
, pv.index
, NULL::text AS property_value_text
, NULL::bytea AS property_value_bytes
, NULL::smallint AS property_value_smallint
, NULL::int AS property_value_int, NULL::bigint AS property_value_bigint
, NULL::numeric AS property_value_numeric
, NULL::real AS property_value_float
, NULL::double precision AS property_value_double
, pv.property_value::boolean AS property_value_boolean
, NULL::date AS property_value_date
, NULL::time AS property_value_time
, NULL::timestamp AS property_value_timestamp
, NULL::uuid AS property_value_uuid
, NULL::jsonb AS property_value_json
FROM property_value_boolean AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk,
pv.collection_pk,
pv.property_pk,
pv.index,
NULL::text AS property_value_text,
NULL::bytea AS property_value_bytes,
NULL::smallint AS property_value_smallint,
NULL::int AS property_value_int,
NULL::bigint AS property_value_bigint,
NULL::numeric AS property_value_numeric,
NULL::real AS property_value_float,
NULL::double precision AS property_value_double,
NULL::boolean AS property_value_boolean,
pv.property_value::date AS property_value_date,
NULL::time AS property_value_time,
NULL::timestamp AS property_value_timestamp,
NULL::uuid AS property_value_uuid,
NULL::jsonb AS property_value_json
FROM property_value_date AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk,
pv.collection_pk,
pv.property_pk,
pv.index,
NULL::text AS property_value_text,
NULL::bytea AS property_value_bytes,
NULL::smallint AS property_value_smallint,
NULL::int AS property_value_int,
NULL::bigint AS property_value_bigint,
NULL::numeric AS property_value_numeric,
NULL::real AS property_value_float,
NULL::double precision AS property_value_double,
NULL::boolean AS property_value_boolean,
NULL::date AS property_value_date,
pv.property_value::time AS property_value_time,
NULL::timestamp AS property_value_timestamp,
NULL::uuid AS property_value_uuid,
NULL::jsonb AS property_value_json
FROM property_value_time AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk,
pv.collection_pk,
pv.property_pk,
pv.index,
NULL::text AS property_value_text,
NULL::bytea AS property_value_bytes,
NULL::smallint AS property_value_smallint,
NULL::int AS property_value_int,
NULL::bigint AS property_value_bigint,
NULL::numeric AS property_value_numeric,
NULL::real AS property_value_float,
NULL::double precision AS property_value_double,
NULL::boolean AS property_value_boolean,
NULL::date AS property_value_date,
NULL::time AS property_value_time,
pv.property_value::timestamp AS property_value_timestamp,
NULL::uuid AS property_value_uuid,
NULL::jsonb AS property_value_json
FROM property_value_timestamp AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk,
pv.collection_pk,
pv.property_pk,
pv.index,
NULL::text AS property_value_text,
NULL::bytea AS property_value_bytes,
NULL::smallint AS property_value_smallint,
NULL::int AS property_value_int,
NULL::bigint AS property_value_bigint,
NULL::numeric AS property_value_numeric,
NULL::real AS property_value_float,
NULL::double precision AS property_value_double,
NULL::boolean AS property_value_boolean,
NULL::date AS property_value_date,
NULL::time AS property_value_time,
NULL::timestamp AS property_value_timestamp,
pv.property_value::uuid AS property_value_uuid,
NULL::jsonb AS property_value_json
FROM property_value_uuid AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)
UNION ALL
SELECT pv.pk,
pv.collection_pk,
pv.property_pk,
pv.index,
NULL::text AS property_value_text,
NULL::bytea AS property_value_bytes,
NULL::smallint AS property_value_smallint,
NULL::int AS property_value_int,
NULL::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric,
NULL::real AS property_value_float,
NULL::double precision AS property_value_double,
NULL::boolean AS property_value_boolean,
NULL::date AS property_value_date,
NULL::time AS property_value_time,
NULL::timestamp AS property_value_timestamp,
NULL::uuid AS property_value_uuid,
pv.property_value::jsonb AS property_value_json
FROM property_value_json AS pv
INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks)
INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks))
/*
CTE7 execution times (includes CTE5, CTE4, CTE3, CTE2, CTE1):
0459 ms
0351 ms
0313 ms
0358 ms
0375 ms
*/
,cte8(top_level_collection_pk, related_collection_pk, property_pk, property_value_bigint, property_value_boolean,
property_value_bytes, property_value_date, property_value_double, property_value_float, property_value_int,
property_value_json, property_value_numeric, property_value_smallint, property_value_text, property_value_time,
property_value_timestamp, property_value_uuid) AS (SELECT cte6.top_level_collection_pk,
cte6.related_collection_pk,
cte6.property_pk,
array_remove(
array_agg(cte7.property_value_bigint ORDER BY cte7.index),
NULL) AS property_value_bigint,
array_remove(
array_agg(cte7.property_value_boolean ORDER BY cte7.index),
NULL) AS property_value_boolean,
array_remove(
array_agg(cte7.property_value_bytes ORDER BY cte7.index),
NULL) AS property_value_bytes,
array_remove(
array_agg(cte7.property_value_date ORDER BY cte7.index),
NULL) AS property_value_date,
array_remove(
array_agg(cte7.property_value_double ORDER BY cte7.index),
NULL) AS property_value_double,
array_remove(
array_agg(cte7.property_value_float ORDER BY cte7.index),
NULL) AS property_value_float,
array_remove(
array_agg(cte7.property_value_int ORDER BY cte7.index),
NULL) AS property_value_int,
array_remove(
array_agg(cte7.property_value_json ORDER BY cte7.index),
NULL) AS property_value_json,
array_remove(
array_agg(cte7.property_value_numeric ORDER BY cte7.index),
NULL) AS property_value_numeric,
array_remove(
array_agg(cte7.property_value_smallint ORDER BY cte7.index),
NULL) AS property_value_smallint,
array_remove(
array_agg(cte7.property_value_text ORDER BY cte7.index),
NULL) AS property_value_text,
array_remove(
array_agg(cte7.property_value_time ORDER BY cte7.index),
NULL) AS property_value_time,
array_remove(
array_agg(cte7.property_value_timestamp ORDER BY cte7.index),
NULL) AS property_value_timestamp,
array_remove(
array_agg(cte7.property_value_uuid ORDER BY cte7.index),
NULL) AS property_value_uuid
FROM cte6
LEFT JOIN cte7 ON (cte6.property_pk =
cte7.property_pk AND
((cte6.top_level_collection_pk =
cte7.collection_pk AND
cte6.related_collection_pk =
'{}') OR
cte7.collection_pk = ANY
(cte6.related_collection_pk)))
GROUP BY cte6.top_level_collection_pk,
cte6.related_collection_pk, cte6.property_pk)
SELECT cte8.property_pk,
cte8.property_value_bigint,
cte8.property_value_boolean,
cte8.property_value_bytes,
cte8.property_value_date,
cte8.property_value_double,
cte8.property_value_float,
cte8.property_value_int,
cte8.property_value_json,
cte8.property_value_numeric,
cte8.property_value_smallint,
cte8.property_value_text,
cte8.property_value_time,
cte8.property_value_timestamp,
cte8.property_value_uuid,
cte8.top_level_collection_pk,
cte8.related_collection_pk
FROM cte8;
/*
CTE8 (Depends on all prior CTEs):
4580 ms
1828 ms
2169 ms
1881 ms
2176 ms
*/
For reference, clean load of the page for this card in GATCG index takes roughly 1s for all elements to be rendered, cached load takes a similar amount.
Observations of SQL below:
SQL with some timing results below each CTE:
EXPLAIN WITH RECURSIVE cte1(top_level_collection_pk, collection_pk, related_collection_pk, index, level) AS (SELECT c.pk AS top_level_collection_pk, c.pk as collection_pk, c.pk AS related_collection_pk, 0 as index, 0 as level FROM collection AS c WHERE c.pk = ANY ('{"961d4453-2d9d-3d07-b1c3-1a5202fc1526","af278b9a-487c-3b16-be8e-2eeaf96681f6","a59b7797-18c8-3a07-801d-550a2f624528","0a33d932-28c8-325d-b978-8ca8f62e53fa","a37db02d-198f-35b1-960e-155743410e01","7cec4bd1-5c49-3ec8-b273-a0405ad13622","3b3eff23-8d95-3c4f-a4dd-c233374b201b","dc74d0ed-0b27-33dc-8ba9-cee32988fcfe","2b628ae7-c61e-34c9-8742-6ed8f124f508","a8cd41ca-5518-3aee-bb39-adac9b5438d9","f1c1b09e-95d3-3bf4-9bb6-f1fced2f800e","045e1932-f2ce-3403-b83b-3cf3ad7ec529","83fc16de-0014-3ff4-8aab-a837466047b8","0bf302ad-214d-391e-a259-1e4c1384beaf","d4b91d84-4ce3-303b-a75a-297dd5e50b16","c83b71da-7bf4-3351-87f6-b6d68d72753d","d8556c10-514a-329d-be73-ae212fefcddd","ce496847-d81c-34df-9b23-832e521189ce","876aff4b-b1e9-379b-9062-32f79247a008","91b7bcee-78c9-322c-9695-bbea31b0c7ea","a007de89-09ee-3547-9074-779ca34b6ffa","75c1bdc8-45c7-3cb5-8130-82eefae797e6","cc4e07c9-8dda-3846-b759-5a71e703d7af","eae9b3e2-bfdc-36e2-bb8d-bf064b11e4bc","874b1847-891b-35ad-b4da-b78f633fd214","5d0a2d46-54e3-3a59-a7c3-88b69b90844c","c39928fb-ef6c-32a2-afed-06bfd3d1b49f","08e7a7cf-fd5a-35f0-b8b0-c86c1055fd97","2234978b-eb88-37b9-be92-7f8fad47ffa0","d1486d3c-86bb-3c4d-ad53-8481b8fdf464","3642a132-2fc1-34ab-a8fe-b99f648e98dd","87b56234-a9b1-354c-9575-33ebe14cfd0c","f403d55d-5eb0-3bd8-bed4-f957437fb150","01b52df1-d622-3462-947d-8558a9108719","a029cbf2-6381-3fc0-b045-24f4452f4ca2","033bd96a-5a27-3a4f-8c16-36fff47eb077","53934f63-69bd-3df7-ba11-abdeb27784a2","5dd44efd-5a97-354f-9727-d2f093ef0177","60e3b277-1da2-3242-8363-f0c7909a675d"}'::uuid[]) UNION SELECT cte1.top_level_collection_pk, r.collection_pk, r.related_collection_pk, r.index, cte1.level + 1 FROM cte1 INNER JOIN relationship AS r ON cte1.related_collection_pk = r.collection_pk WHERE r.relationship_type = 'SourceOfPropertiesAndPropertyValues') /* CTE1 execution times: 0224 ms 0277 ms 0369 ms 0355 ms 0322 ms */ ,cte2(distinct_collection_pk) AS (SELECT DISTINCT ON (distinct_collection_pk) coalesce(cte1.related_collection_pk, cte1.top_level_collection_pk) AS distinct_collection_pk FROM cte1) /* CTE2 execution times (includes CTE1): 0133 ms 0265 ms 0263 ms 0146 ms 0326 ms */ ,cte3(distinct_collection_pks) AS (SELECT array_agg(cte2.distinct_collection_pk) AS distiction_collection_pks FROM cte2) /* CTE3 execution times (includes CTE2): 0377 ms 0199 ms 0266 ms 0216 ms 0272 ms */ ,cte4(distinct_property_pk) AS (SELECT distinct on (pc.property_pk) pc.property_pk FROM property_collection AS pc INNER JOIN cte2 ON cte2.distinct_collection_pk = pc.collection_pk) /* CTE4 execution times (includes CTE2): 0294 ms 0272 ms 0174 ms 0316 ms 0116 ms */ ,cte5(distinct_property_pks) AS (SELECT array_agg(cte4.distinct_property_pk) AS distinct_property_pks FROM cte4) /* CTE5 execution times (includes CTE4, CTE2): 0114 ms 0257 ms 0274 ms 0103 ms 0270 ms */ ,cte6(top_level_collection_pk, related_collection_pk, property_pk) AS (SELECT top_level_collection_pk, array_remove(array_agg(related_collection_pk), NULL) as related_collection_pk, property_pk FROM cte1 LEFT JOIN property_collection AS pc ON (cte1.top_level_collection_pk = pc.collection_pk OR pc.collection_pk = cte1.related_collection_pk) WHERE pc.property_pk IS NOT NULL GROUP BY top_level_collection_pk, property_pk) /* CTE6 execution times (includes CTE1): 0582 ms 0792 ms 0699 ms 0788 ms 0700 ms */ ,cte7(pk, collection_pk, property_pk, index, property_value_text, property_value_bytes, property_value_smallint, property_value_int, property_value_bigint, property_value_numeric, property_value_float, property_value_double, property_value_boolean, property_value_date, property_value_time, property_value_timestamp, property_value_uuid, property_value_json) AS (SELECT pv.pk, pv.collection_pk, pv.property_pk, pv.index, pv.property_value::text AS property_value_text, NULL::bytea AS property_value_bytes, NULL::smallint AS property_value_smallint, NULL::int AS property_value_int, NULL::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric, NULL::real AS property_value_float, NULL::double precision AS property_value_double, NULL::boolean AS property_value_boolean, NULL::date AS property_value_date, NULL::time AS property_value_time, NULL::timestamp AS property_value_timestamp, NULL ::uuid AS property_value_uuid, NULL::jsonb AS property_value_json FROM property_value_text AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk, pv.collection_pk, pv.property_pk, pv.index, NULL::text AS property_value_text, pv.property_value::bytea AS property_value_bytes, NULL::smallint AS property_value_smallint, NULL::int AS property_value_int, NULL::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric, NULL::real AS property_value_float, NULL::double precision AS property_value_double, NULL::boolean AS property_value_boolean, NULL::date AS property_value_date, NULL::time AS property_value_time, NULL::timestamp AS property_value_timestamp, NULL::uuid AS property_value_uuid, NULL::jsonb AS property_value_json FROM property_value_bytes AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk, pv.collection_pk, pv.property_pk, pv.index, NULL::text AS property_value_text, NULL::bytea AS property_value_bytes, pv.property_value::smallint AS property_value_smallint, NULL::int AS property_value_int, NULL::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric, NULL::real AS property_value_float, NULL::double precision AS property_value_double, NULL::boolean AS property_value_boolean, NULL::date AS property_value_date, NULL::time AS property_value_time, NULL::timestamp AS property_value_timestamp, NULL::uuid AS property_value_uuid, NULL::jsonb AS property_value_json FROM property_value_smallint AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk , pv.collection_pk , pv.property_pk , pv.index , NULL::text AS property_value_text , NULL::bytea AS property_value_bytes , NULL::smallint AS property_value_smallint, pv.property_value::int AS property_value_int , NULL::bigint AS property_value_bigint , NULL::numeric AS property_value_numeric , NULL::real AS property_value_float , NULL::double precision AS property_value_double , NULL::boolean AS property_value_boolean , NULL::date AS property_value_date , NULL::time AS property_value_time , NULL::timestamp AS property_value_timestamp , NULL::uuid AS property_value_uuid , NULL::jsonb AS property_value_json FROM property_value_int AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk, pv.collection_pk, pv.property_pk, pv.index, NULL::text AS property_value_text, NULL::bytea AS property_value_bytes, NULL::smallint AS property_value_smallint, NULL::int AS property_value_int, pv.property_value::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric, NULL::real AS property_value_float, NULL::double precision AS property_value_double, NULL::boolean AS property_value_boolean, NULL::date AS property_value_date, NULL::time AS property_value_time, NULL::timestamp AS property_value_timestamp, NULL::uuid AS property_value_uuid, NULL::jsonb AS property_value_json FROM property_value_bigint AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk , pv.collection_pk , pv.property_pk , pv.index , NULL::text AS property_value_text , NULL::bytea AS property_value_bytes , NULL::smallint AS property_value_smallint , NULL::int AS property_value_int , NULL::bigint AS property_value_bigint , pv.property_value::numeric AS property_value_numeric , NULL::real AS property_value_float , NULL::double precision AS property_value_double , NULL::boolean AS property_value_boolean , NULL::date AS property_value_date , NULL::time AS property_value_time , NULL::timestamp AS property_value_timestamp , NULL::uuid AS property_value_uuid , NULL::jsonb AS property_value_json FROM property_value_numeric AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk, pv.collection_pk, pv.property_pk, pv.index, NULL::text AS property_value_text, NULL::bytea AS property_value_bytes, NULL::smallint AS property_value_smallint, NULL::int AS property_value_int, NULL::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric, pv.property_value::real AS property_value_float, NULL::double precision AS property_value_double, NULL::boolean AS property_value_boolean, NULL::date AS property_value_date, NULL::time AS property_value_time, NULL::timestamp AS property_value_timestamp, NULL::uuid AS property_value_uuid, NULL::jsonb AS property_value_json FROM property_value_float AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk, pv.collection_pk, pv.property_pk, pv.index, NULL::text AS property_value_text, NULL::bytea AS property_value_bytes, NULL::smallint AS property_value_smallint, NULL::int AS property_value_int, NULL::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric, NULL::real AS property_value_float, pv.property_value::double precision AS property_value_double, NULL::boolean AS property_value_boolean, NULL::date AS property_value_date, NULL::time AS property_value_time, NULL::timestamp AS property_value_timestamp, NULL::uuid AS property_value_uuid, NULL::jsonb AS property_value_json FROM property_value_double AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk , pv.collection_pk , pv.property_pk , pv.index , NULL::text AS property_value_text , NULL::bytea AS property_value_bytes , NULL::smallint AS property_value_smallint , NULL::int AS property_value_int, NULL::bigint AS property_value_bigint , NULL::numeric AS property_value_numeric , NULL::real AS property_value_float , NULL::double precision AS property_value_double , pv.property_value::boolean AS property_value_boolean , NULL::date AS property_value_date , NULL::time AS property_value_time , NULL::timestamp AS property_value_timestamp , NULL::uuid AS property_value_uuid , NULL::jsonb AS property_value_json FROM property_value_boolean AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk, pv.collection_pk, pv.property_pk, pv.index, NULL::text AS property_value_text, NULL::bytea AS property_value_bytes, NULL::smallint AS property_value_smallint, NULL::int AS property_value_int, NULL::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric, NULL::real AS property_value_float, NULL::double precision AS property_value_double, NULL::boolean AS property_value_boolean, pv.property_value::date AS property_value_date, NULL::time AS property_value_time, NULL::timestamp AS property_value_timestamp, NULL::uuid AS property_value_uuid, NULL::jsonb AS property_value_json FROM property_value_date AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk, pv.collection_pk, pv.property_pk, pv.index, NULL::text AS property_value_text, NULL::bytea AS property_value_bytes, NULL::smallint AS property_value_smallint, NULL::int AS property_value_int, NULL::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric, NULL::real AS property_value_float, NULL::double precision AS property_value_double, NULL::boolean AS property_value_boolean, NULL::date AS property_value_date, pv.property_value::time AS property_value_time, NULL::timestamp AS property_value_timestamp, NULL::uuid AS property_value_uuid, NULL::jsonb AS property_value_json FROM property_value_time AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk, pv.collection_pk, pv.property_pk, pv.index, NULL::text AS property_value_text, NULL::bytea AS property_value_bytes, NULL::smallint AS property_value_smallint, NULL::int AS property_value_int, NULL::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric, NULL::real AS property_value_float, NULL::double precision AS property_value_double, NULL::boolean AS property_value_boolean, NULL::date AS property_value_date, NULL::time AS property_value_time, pv.property_value::timestamp AS property_value_timestamp, NULL::uuid AS property_value_uuid, NULL::jsonb AS property_value_json FROM property_value_timestamp AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk, pv.collection_pk, pv.property_pk, pv.index, NULL::text AS property_value_text, NULL::bytea AS property_value_bytes, NULL::smallint AS property_value_smallint, NULL::int AS property_value_int, NULL::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric, NULL::real AS property_value_float, NULL::double precision AS property_value_double, NULL::boolean AS property_value_boolean, NULL::date AS property_value_date, NULL::time AS property_value_time, NULL::timestamp AS property_value_timestamp, pv.property_value::uuid AS property_value_uuid, NULL::jsonb AS property_value_json FROM property_value_uuid AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks) UNION ALL SELECT pv.pk, pv.collection_pk, pv.property_pk, pv.index, NULL::text AS property_value_text, NULL::bytea AS property_value_bytes, NULL::smallint AS property_value_smallint, NULL::int AS property_value_int, NULL::bigint AS property_value_bigint, NULL::numeric AS property_value_numeric, NULL::real AS property_value_float, NULL::double precision AS property_value_double, NULL::boolean AS property_value_boolean, NULL::date AS property_value_date, NULL::time AS property_value_time, NULL::timestamp AS property_value_timestamp, NULL::uuid AS property_value_uuid, pv.property_value::jsonb AS property_value_json FROM property_value_json AS pv INNER JOIN cte3 ON pv.collection_pk = ANY (cte3.distinct_collection_pks) INNER JOIN cte5 ON pv.property_pk = ANY (cte5.distinct_property_pks)) /* CTE7 execution times (includes CTE5, CTE4, CTE3, CTE2, CTE1): 0459 ms 0351 ms 0313 ms 0358 ms 0375 ms */ ,cte8(top_level_collection_pk, related_collection_pk, property_pk, property_value_bigint, property_value_boolean, property_value_bytes, property_value_date, property_value_double, property_value_float, property_value_int, property_value_json, property_value_numeric, property_value_smallint, property_value_text, property_value_time, property_value_timestamp, property_value_uuid) AS (SELECT cte6.top_level_collection_pk, cte6.related_collection_pk, cte6.property_pk, array_remove( array_agg(cte7.property_value_bigint ORDER BY cte7.index), NULL) AS property_value_bigint, array_remove( array_agg(cte7.property_value_boolean ORDER BY cte7.index), NULL) AS property_value_boolean, array_remove( array_agg(cte7.property_value_bytes ORDER BY cte7.index), NULL) AS property_value_bytes, array_remove( array_agg(cte7.property_value_date ORDER BY cte7.index), NULL) AS property_value_date, array_remove( array_agg(cte7.property_value_double ORDER BY cte7.index), NULL) AS property_value_double, array_remove( array_agg(cte7.property_value_float ORDER BY cte7.index), NULL) AS property_value_float, array_remove( array_agg(cte7.property_value_int ORDER BY cte7.index), NULL) AS property_value_int, array_remove( array_agg(cte7.property_value_json ORDER BY cte7.index), NULL) AS property_value_json, array_remove( array_agg(cte7.property_value_numeric ORDER BY cte7.index), NULL) AS property_value_numeric, array_remove( array_agg(cte7.property_value_smallint ORDER BY cte7.index), NULL) AS property_value_smallint, array_remove( array_agg(cte7.property_value_text ORDER BY cte7.index), NULL) AS property_value_text, array_remove( array_agg(cte7.property_value_time ORDER BY cte7.index), NULL) AS property_value_time, array_remove( array_agg(cte7.property_value_timestamp ORDER BY cte7.index), NULL) AS property_value_timestamp, array_remove( array_agg(cte7.property_value_uuid ORDER BY cte7.index), NULL) AS property_value_uuid FROM cte6 LEFT JOIN cte7 ON (cte6.property_pk = cte7.property_pk AND ((cte6.top_level_collection_pk = cte7.collection_pk AND cte6.related_collection_pk = '{}') OR cte7.collection_pk = ANY (cte6.related_collection_pk))) GROUP BY cte6.top_level_collection_pk, cte6.related_collection_pk, cte6.property_pk) SELECT cte8.property_pk, cte8.property_value_bigint, cte8.property_value_boolean, cte8.property_value_bytes, cte8.property_value_date, cte8.property_value_double, cte8.property_value_float, cte8.property_value_int, cte8.property_value_json, cte8.property_value_numeric, cte8.property_value_smallint, cte8.property_value_text, cte8.property_value_time, cte8.property_value_timestamp, cte8.property_value_uuid, cte8.top_level_collection_pk, cte8.related_collection_pk FROM cte8; /* CTE8 (Depends on all prior CTEs): 4580 ms 1828 ms 2169 ms 1881 ms 2176 ms */