QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=845564.29..845578.89 rows=5839 width=176) (actual time=892.849..892.891 rows=85 loops=1) Output: (1), lookup.internalid, lookup.rownumber, lookup.publicid, (NULL::text), (NULL::text), (NULL::bytea), (NULL::integer), (NULL::integer), (NULL::integer), (NULL::bigint), (NULL::bigint) Sort Key: (1), lookup.rownumber Sort Method: quicksort Memory: 30kB Buffers: shared hit=188 read=118 dirtied=1 CTE lookup -> WindowAgg (cost=33.66..33.68 rows=1 width=61) (actual time=824.826..824.871 rows=4 loops=1) Output: studies.publicid, studies.internalid, row_number() OVER (?) Buffers: shared hit=44 read=29 -> Sort (cost=33.66..33.66 rows=1 width=53) (actual time=824.719..824.729 rows=4 loops=1) Output: studies.publicid, studies.internalid Sort Key: studies.publicid Sort Method: quicksort Memory: 25kB Buffers: shared hit=44 read=29 -> Nested Loop (cost=2.41..33.65 rows=1 width=53) (actual time=814.789..824.677 rows=4 loops=1) Output: studies.publicid, studies.internalid Inner Unique: true Join Filter: (studies.internalid = t0.id) Buffers: shared hit=44 read=29 -> Nested Loop (cost=1.84..25.47 rows=1 width=69) (actual time=814.754..824.599 rows=4 loops=1) Output: studies.publicid, studies.internalid, t1.id, t2.id Inner Unique: true Buffers: shared hit=24 read=29 -> Nested Loop (cost=1.27..17.31 rows=1 width=16) (actual time=814.047..820.373 rows=4 loops=1) Output: t1.id, t2.id Inner Unique: true Buffers: shared hit=16 read=17 -> Index Scan using dicomidentifiersindex3 on public.dicomidentifiers t2 (cost=0.70..8.72 rows=1 width=8) (actual time=2.046..4.138 rows=5 loops=1) Output: t2.id, t2.taggroup, t2.tagelement, t2.value Index Cond: ((t2.taggroup = 16) AND (t2.tagelement = 32) AND (t2.value = '202400846'::text)) Buffers: shared hit=4 read=5 -> Index Scan using dicomidentifiers_pkey on public.dicomidentifiers t1 (cost=0.57..8.59 rows=1 width=8) (actual time=1.509..1.509 rows=1 loops=5) Output: t1.id, t1.taggroup, t1.tagelement, t1.value Index Cond: ((t1.id = t2.id) AND (t1.taggroup = 8) AND (t1.tagelement = 32)) Filter: (t1.value <= '20241231'::text) Buffers: shared hit=12 read=12 -> Index Scan using resources_pkey on public.resources studies (cost=0.57..8.15 rows=1 width=53) (actual time=1.049..1.049 rows=1 loops=4) Output: studies.internalid, studies.resourcetype, studies.publicid, studies.parentid, studies.childcount Index Cond: (studies.internalid = t1.id) Filter: (studies.resourcetype = 1) Buffers: shared hit=8 read=12 -> Index Scan using dicomidentifiers_pkey on public.dicomidentifiers t0 (cost=0.57..8.17 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=4) Output: t0.id, t0.taggroup, t0.tagelement, t0.value Index Cond: ((t0.id = t1.id) AND (t0.taggroup = 8) AND (t0.tagelement = 32)) Filter: (t0.value >= '20240101'::text) Buffers: shared hit=20 -> Append (cost=0.00..845165.34 rows=5839 width=176) (actual time=824.847..892.655 rows=85 loops=1) Buffers: shared hit=188 read=118 dirtied=1 -> CTE Scan on lookup (cost=0.00..0.02 rows=1 width=290) (actual time=824.844..824.886 rows=4 loops=1) Output: 1, lookup.internalid, lookup.rownumber, lookup.publicid, NULL::text, NULL::text, NULL::bytea, NULL::integer, NULL::integer, NULL::integer, NULL::bigint, NULL::bigint Buffers: shared hit=44 read=29 -> Subquery Scan on "*SELECT* 2" (cost=0.58..5196.66 rows=1972 width=176) (actual time=4.106..12.361 rows=49 loops=1) Output: 2, "*SELECT* 2".c1_internalid, NULL::bigint, "*SELECT* 2".c3_string1, NULL::text, NULL::text, NULL::bytea, "*SELECT* 2".c7_int1, "*SELECT* 2".c8_int2, NULL::integer, NULL::bigint, NULL::bigint Buffers: shared hit=6 read=16 -> Nested Loop (cost=0.58..5176.94 rows=1972 width=165) (actual time=4.092..12.326 rows=49 loops=1) Output: 2, lookup_1.internalid, NULL::bigint, maindicomtags.value, NULL::text, NULL::text, NULL::bytea, maindicomtags.taggroup, maindicomtags.tagelement, NULL::integer, NULL::bigint, NULL::bigint Buffers: shared hit=6 read=16 -> CTE Scan on lookup lookup_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.008 rows=4 loops=1) Output: lookup_1.publicid, lookup_1.internalid, lookup_1.rownumber -> Index Scan using maindicomtagsindex on public.maindicomtags (cost=0.58..5157.20 rows=1972 width=37) (actual time=2.820..3.053 rows=12 loops=4) Output: maindicomtags.id, maindicomtags.taggroup, maindicomtags.tagelement, maindicomtags.value Index Cond: (maindicomtags.id = lookup_1.internalid) Buffers: shared hit=6 read=16 -> Subquery Scan on "*SELECT* 3" (cost=0.58..10016.34 rows=3848 width=176) (actual time=6.246..18.758 rows=8 loops=1) Output: 4, "*SELECT* 3".c1_internalid, NULL::bigint, "*SELECT* 3".c3_string1, NULL::text, NULL::text, NULL::bytea, "*SELECT* 3".c7_int1, "*SELECT* 3".c8_int2, NULL::integer, NULL::bigint, NULL::bigint Buffers: shared hit=4 read=19 -> Nested Loop (cost=0.58..9977.86 rows=3848 width=199) (actual time=6.237..18.740 rows=8 loops=1) Output: 4, lookup_2.internalid, NULL::bigint, metadata.value, NULL::text, NULL::text, NULL::bytea, metadata.type, metadata.revision, NULL::integer, NULL::bigint, NULL::bigint Buffers: shared hit=4 read=19 -> CTE Scan on lookup lookup_2 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.007 rows=4 loops=1) Output: lookup_2.publicid, lookup_2.internalid, lookup_2.rownumber -> Index Scan using metadata_pkey on public.metadata (cost=0.58..9939.36 rows=3848 width=71) (actual time=4.273..4.656 rows=2 loops=4) Output: metadata.id, metadata.type, metadata.value, metadata.revision Index Cond: (metadata.id = lookup_2.internalid) Buffers: shared hit=4 read=19 -> Subquery Scan on "*SELECT* 4" (cost=1.15..19757.90 rows=17 width=176) (actual time=6.336..36.034 rows=20 loops=1) Output: 21, "*SELECT* 4".c1_internalid, NULL::bigint, "*SELECT* 4".c3_string1, NULL::text, NULL::text, NULL::bytea, "*SELECT* 4".c7_int1, "*SELECT* 4".c8_int2, NULL::integer, NULL::bigint, NULL::bigint Buffers: shared hit=78 read=54 dirtied=1 -> Nested Loop (cost=1.15..19757.73 rows=17 width=165) (actual time=6.325..35.998 rows=20 loops=1) Output: 21, lookup_3.internalid, NULL::bigint, maindicomtags_1.value, NULL::text, NULL::text, NULL::bytea, maindicomtags_1.taggroup, maindicomtags_1.tagelement, NULL::integer, NULL::bigint, NULL::bigint Inner Unique: true Buffers: shared hit=78 read=54 dirtied=1 -> Nested Loop (cost=0.57..257.11 rows=3238 width=16) (actual time=2.806..9.813 rows=20 loops=1) Output: lookup_3.internalid, childlevel.internalid Buffers: shared hit=14 read=18 dirtied=1 -> CTE Scan on lookup lookup_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.008 rows=4 loops=1) Output: lookup_3.publicid, lookup_3.internalid, lookup_3.rownumber -> Index Only Scan using childrenindex2 on public.resources childlevel (cost=0.57..224.71 rows=3238 width=16) (actual time=2.382..2.426 rows=5 loops=4) Output: childlevel.parentid, childlevel.publicid, childlevel.internalid Index Cond: (childlevel.parentid = lookup_3.internalid) Heap Fetches: 22 Buffers: shared hit=14 read=18 dirtied=1 -> Index Scan using maindicomtags_pkey on public.maindicomtags maindicomtags_1 (cost=0.58..11.47 rows=2 width=37) (actual time=1.303..1.303 rows=1 loops=20) Output: maindicomtags_1.id, maindicomtags_1.taggroup, maindicomtags_1.tagelement, maindicomtags_1.value Index Cond: ((maindicomtags_1.id = childlevel.internalid) AND (maindicomtags_1.taggroup = 8) AND (maindicomtags_1.tagelement = 96)) Buffers: shared hit=64 read=36 -> Subquery Scan on "*SELECT* 5" (cost=0.00..810165.22 rows=1 width=176) (actual time=0.381..0.557 rows=4 loops=1) Output: 33, "*SELECT* 5".c1_internalid, NULL::bigint, NULL::character varying, NULL::text, NULL::text, NULL::bytea, NULL::integer, NULL::integer, NULL::integer, "*SELECT* 5".c10_big_int1, NULL::bigint Buffers: shared hit=56 -> CTE Scan on lookup lookup_4 (cost=0.00..810165.21 rows=1 width=176) (actual time=0.370..0.543 rows=4 loops=1) Output: 33, lookup_4.internalid, NULL::bigint, NULL::text, NULL::text, NULL::text, NULL::bytea, NULL::integer, NULL::integer, NULL::integer, COALESCE((SubPlan 2), (SubPlan 3)), NULL::bigint Buffers: shared hit=56 SubPlan 2 -> Subquery Scan on validgrandchildcounts (cost=266.48..1936.74 rows=1 width=8) (actual time=0.112..0.114 rows=1 loops=4) Output: validgrandchildcounts.totalgrandchildcount Buffers: shared hit=56 -> GroupAggregate (cost=266.48..1936.73 rows=1 width=16) (actual time=0.108..0.109 rows=1 loops=4) Output: thislevel.internalid, COALESCE(sum(childlevel_1.childcount), '0'::bigint) Buffers: shared hit=56 -> Nested Loop Left Join (cost=266.48..1936.72 rows=1 width=12) (actual time=0.091..0.101 rows=5 loops=4) Output: thislevel.internalid, childlevel_1.childcount Buffers: shared hit=56 -> Nested Loop Anti Join (cost=265.91..311.83 rows=1 width=8) (actual time=0.063..0.066 rows=1 loops=4) Output: thislevel.internalid Buffers: shared hit=30 -> Nested Loop Anti Join (cost=265.76..303.61 rows=1 width=8) (actual time=0.045..0.047 rows=1 loops=4) Output: thislevel.internalid Buffers: shared hit=22 -> Index Only Scan using resources_pkey on public.resources thislevel (cost=0.57..8.59 rows=1 width=8) (actual time=0.031..0.032 rows=1 loops=4) Output: thislevel.internalid Index Cond: (thislevel.internalid = lookup_4.internalid) Heap Fetches: 4 Buffers: shared hit=22 -> Hash Join (cost=265.19..295.01 rows=1 width=8) (actual time=0.005..0.006 rows=0 loops=4) Output: child.parentid Inner Unique: true Hash Cond: (invalidchildcounts_1.id = child.internalid) -> Seq Scan on public.invalidchildcounts invalidchildcounts_1 (cost=0.00..25.70 rows=1570 width=8) (actual time=0.005..0.005 rows=0 loops=4) Output: invalidchildcounts_1.id, invalidchildcounts_1.updatedat, invalidchildcounts_1.pk -> Hash (cost=224.71..224.71 rows=3238 width=16) (never executed) Output: child.internalid, child.parentid -> Index Only Scan using childrenindex2 on public.resources child (cost=0.57..224.71 rows=3238 width=16) (never executed) Output: child.internalid, child.parentid Index Cond: (child.parentid = lookup_4.internalid) Heap Fetches: 0 -> Index Only Scan using invalidchildcountsid on public.invalidchildcounts (cost=0.15..8.21 rows=3 width=8) (actual time=0.012..0.012 rows=0 loops=4) Output: invalidchildcounts.id Index Cond: (invalidchildcounts.id = lookup_4.internalid) Heap Fetches: 0 Buffers: shared hit=8 -> Index Scan using childrenindex2 on public.resources childlevel_1 (cost=0.57..1592.51 rows=3238 width=12) (actual time=0.019..0.024 rows=5 loops=4) Output: childlevel_1.internalid, childlevel_1.resourcetype, childlevel_1.publicid, childlevel_1.parentid, childlevel_1.childcount Index Cond: (childlevel_1.parentid = lookup_4.internalid) Buffers: shared hit=26 SubPlan 3 -> Aggregate (cost=808228.44..808228.45 rows=1 width=8) (never executed) Output: count(grandchildlevel.internalid) -> Nested Loop (cost=1.14..808220.34 rows=3238 width=8) (never executed) Output: grandchildlevel.internalid -> Index Only Scan using childrenindex2 on public.resources childlevel_2 (cost=0.57..224.71 rows=3238 width=8) (never executed) Output: childlevel_2.parentid, childlevel_2.publicid, childlevel_2.internalid Index Cond: (childlevel_2.parentid = lookup_4.internalid) Heap Fetches: 0 -> Index Only Scan using childrenindex2 on public.resources grandchildlevel (cost=0.57..217.16 rows=3238 width=16) (never executed) Output: grandchildlevel.parentid, grandchildlevel.publicid, grandchildlevel.internalid Index Cond: (grandchildlevel.parentid = childlevel_2.internalid) Heap Fetches: 0 Planning: Buffers: shared hit=247 read=6 Planning Time: 19.954 ms JIT: Functions: 93 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 14.773 ms (Deform 5.366 ms), Inlining 47.679 ms, Optimization 413.590 ms, Emission 347.844 ms, Total 823.886 ms Execution Time: 909.059 ms (165 rows)