SELECT t1.*
	-- ,(ut2).* 
 FROM table1 t1
,lateral (select count( distinct t2.elementcode) countMyelement
			--array_agg( (t2.*)::table2) AS table2_s
	FROM table2 t2 
	where t1.id = t2.id_table1 
		AND t2.elementcode = any(array['1','12','17'])) t2
-- ,lateral unnest(table2_s) AS ut2
WHERE countMyelement = 3

SELECT t1.*
	,(ut2).* 
 FROM
	(select
			id_table1
			--,count( distinct t2.elementcode) countMyelement
			,array_agg( (t2.*)::table2) AS table2_s
	FROM table2 t2 
	where
		t2.elementcode = any(array['1','12','17'])
	group by t2.id_table1
	having count( distinct t2.elementcode) = 3
	) t2 join table1 t1
		on t1.id = t2.id_table1
,lateral unnest(table2_s) AS ut2

with table2 (elementcode,qty,id_table1) as (values
	( 1,1 ,1 )
	,(17,1,1)
	,(12,1,1)
	,(1,1,2)
	,(17,1,2)
	,(12,1,2)
	,(13,1,2)
	,(17,1,3)
	,(17,1,3)
	,(1,1,3)
	)
,table1 (id ,description) as (VALUES 
	 (1 ,1,0)
	,(2 ,2,0)
	,(3 ,3,0)
	)
-- собсна запрос
select t1.*, (ut).* from table1 t1
join (select id_table1, array_agg(t2.*) t2_s from table2 t2 group by id_table1
	having array_agg(distinct elementcode order by elementcode) = array[1,12,17])t2
on t1.id = t2.id_table1
-- + детали
,lateral unnest(t2_s) ut (elementcode int,qty int, id_tq int)

--- tables
create table table2 
(elementcode int,qty int, id_table1 int)
;
create table table1 
(id int,description text)
;
----------- data --
insert into table2 
SELECT
 (30 *RANDOM() )::INT + ge
, sum(1) as cnt , gI
from generate_series(1,1000000) gi, generate_series(1,4) gE
group by 1,3;

insert into table1 
select id_table1, array_agg (elementcode )::text from TABLE2 group by id_table1 
;
---indexes --
create index on table1 (id);

create index on table2 (id_table1,elementcode);

create index on table2 (elementcode);

select t1.*, (ut).* from table1 t1
join (select id_table1, array_agg(t2.*) t2_s from table2 t2 group by id_table1
	having array_agg(distinct elementcode order by elementcode) = array[1,13,17])t2
	on t1.id = t2.id_table1
,lateral unnest(t2_s) ut ;
------------
'Nested Loop  (cost=0.86..2331058.18 rows=102358600 width=28) (actual time=1737.914..6231.921 rows=15 loops=1)'
'  Output: t1.id, t1.description, ut.elementcode, ut.qty, ut.id_table1'
'  Buffers: shared hit=2995734 read=838373 written=11'
'  ->  Merge Join  (cost=0.85..283886.18 rows=1023586 width=48) (actual time=1737.899..6231.843 rows=5 loops=1)'
'        Output: t1.id, t1.description, (array_agg(t2.*))'
'        Merge Cond: (t2.id_table1 = t1.id)'
'        Buffers: shared hit=2995734 read=838373 written=11'
'        ->  GroupAggregate  (cost=0.43..225532.14 rows=1023586 width=36) (actual time=1691.565..6068.506 rows=5 loops=1)'
'              Output: t2.id_table1, array_agg(t2.*)'
'              Group Key: t2.id_table1'
'              Filter: (array_agg(DISTINCT t2.elementcode ORDER BY t2.elementcode) = '{1,13,17}'::integer[])'
'              Rows Removed by Filter: 999995'
'              Buffers: shared hit=2995734 read=829695 written=11'
'              ->  Index Scan using table2_id_table1_elementcode_idx on public.table2 t2  (cost=0.43..181565.47 rows=3815051 width=44) (actual time=0.015..4164.226 rows=3815051 loops=1)'
'                    Output: t2.id_table1, t2.*, t2.elementcode'
'                    Buffers: shared hit=2995734 read=829695 written=11'
'        ->  Index Scan using table1_id_idx on public.table1 t1  (cost=0.42..32009.42 rows=1000000 width=16) (actual time=0.017..111.499 rows=990588 loops=1)'
'              Output: t1.id, t1.description'
'              Buffers: shared read=8678'
'  ->  Function Scan on pg_catalog.unnest ut  (cost=0.00..1.00 rows=100 width=12) (actual time=0.011..0.011 rows=3 loops=5)'
'        Output: ut.elementcode, ut.qty, ut.id_table1'
'        Function Call: unnest((array_agg(t2.*)))'
'Planning time: 0.180 ms'
'Execution time: 6231.957 ms'

select * from table1 where 
exists(
select 1
from table2
where table1.id=table2.id_table1  
group by id_table1 
having
    count(case when elementcode in (1,13,17) then 1 else null end)=3 and count(*)=3 and count(distinct elementcode)=3
);
--------------------------
'Seq Scan on public.table1  (cost=0.00..5487240.77 rows=500000 width=16) (actual time=1957.146..6962.123 rows=5 loops=1)'
'  Output: table1.id, table1.description'
'  Filter: (SubPlan 1)'
'  Rows Removed by Filter: 999995'
'  Buffers: shared hit=5976753 read=857564 written=12'
'  SubPlan 1'
'    ->  GroupAggregate  (cost=0.43..20.59 rows=4 width=8) (actual time=0.007..0.007 rows=0 loops=1000000)'
'          Output: 1, table2.id_table1'
'          Group Key: table2.id_table1'
'          Filter: ((count(CASE WHEN (table2.elementcode = ANY ('{1,13,17}'::integer[])) THEN 1 ELSE NULL::integer END) = 3) AND (count(*) = 3) AND (count(DISTINCT table2.elementcode) = 3))'
'          Rows Removed by Filter: 1'
'          Buffers: shared hit=5976753 read=851538 written=12'
'          ->  Index Only Scan using table2_id_table1_elementcode_idx on public.table2  (cost=0.43..20.50 rows=4 width=8) (actual time=0.002..0.005 rows=4 loops=1000000)'
'                Output: table2.id_table1, table2.elementcode'
'                Index Cond: (table2.id_table1 = table1.id)'
'                Heap Fetches: 3815051'
'                Buffers: shared hit=5976753 read=851538 written=12'
'Planning time: 0.140 ms'
'Execution time: 6962.178 ms'

SELECT t1.*
	,(ut2).* 
 FROM
	(select
			id_table1
			,array_agg( (t2.*)::table2) AS table2_s
	FROM table2 t2 
	where
		t2.elementcode = any(array[1,13,17])
		-- и только они:
		and not exists(select 1 from table2 t22
				 where not t22.elementcode  = any(array[1,13,17])
						and t22.id_table1 =t2.id_table1) 
	group by t2.id_table1
	having count( distinct t2.elementcode) = 3 -- все 3 из списка
	) t2 join table1 t1
		on t1.id = t2.id_table1
,lateral unnest(table2_s) AS ut2
--------------------------
'Nested Loop  (cost=91779.07..91789.13 rows=100 width=28) (actual time=479.945..480.076 rows=15 loops=1)'
'  Output: t1.id, t1.description, ut2.elementcode, ut2.qty, ut2.id_table1'
'  Buffers: shared hit=947835 read=247627'
'  ->  Nested Loop  (cost=91779.07..91787.13 rows=1 width=48) (actual time=479.932..480.045 rows=5 loops=1)'
'        Output: (array_agg(t2.*)), t1.id, t1.description'
'        Buffers: shared hit=947835 read=247627'
'        ->  GroupAggregate  (cost=91778.64..91778.67 rows=1 width=36) (actual time=479.905..479.956 rows=5 loops=1)'
'              Output: t2.id_table1, array_agg(t2.*)'
'              Group Key: t2.id_table1'
'              Filter: (count(DISTINCT t2.elementcode) = 3)'
'              Rows Removed by Filter: 17'
'              Buffers: shared hit=947831 read=247611'
'              ->  Sort  (cost=91778.64..91778.65 rows=1 width=44) (actual time=479.846..479.853 rows=46 loops=1)'
'                    Output: t2.id_table1, t2.*, t2.elementcode'
'                    Sort Key: t2.id_table1'
'                    Sort Method: quicksort  Memory: 28kB'
'                    Buffers: shared hit=947831 read=247611'
'                    ->  Gather  (cost=1000.43..91778.63 rows=1 width=44) (actual time=25.767..479.778 rows=46 loops=1)'
'                          Output: t2.id_table1, t2.*, t2.elementcode'
'                          Workers Planned: 3'
'                          Workers Launched: 3'
'                          Buffers: shared hit=947831 read=247611'
'                          ->  Nested Loop Anti Join  (cost=0.43..90778.53 rows=1 width=44) (actual time=57.059..476.593 rows=12 loops=4)'
'                                Output: t2.id_table1, t2.*, t2.elementcode'
'                                Buffers: shared hit=947509 read=247588'
'                                Worker 0: actual time=8.714..475.627 rows=14 loops=1'
'                                  Buffers: shared hit=239038 read=62744'
'                                Worker 1: actual time=141.433..475.898 rows=11 loops=1'
'                                  Buffers: shared hit=243513 read=63600'
'                                Worker 2: actual time=52.621..475.507 rows=7 loops=1'
'                                  Buffers: shared hit=232273 read=60491'
'                                ->  Parallel Seq Scan on public.table2 t2  (cost=0.00..37543.60 rows=85244 width=44) (actual time=0.034..98.323 rows=67634 loops=4)'
'                                      Output: t2.id_table1, t2.*, t2.elementcode'
'                                      Filter: (t2.elementcode = ANY ('{1,13,17}'::integer[]))'
'                                      Rows Removed by Filter: 886128'
'                                      Buffers: shared hit=5440 read=15182'
'                                      Worker 0: actual time=0.040..97.271 rows=68359 loops=1'
'                                        Buffers: shared hit=1338 read=3872'
'                                      Worker 1: actual time=0.025..95.426 rows=69505 loops=1'
'                                        Buffers: shared hit=1393 read=3898'
'                                      Worker 2: actual time=0.039..98.369 rows=66294 loops=1'
'                                        Buffers: shared hit=1385 read=3648'
'                                ->  Index Only Scan using table2_id_table1_elementcode_idx on public.table2 t22  (cost=0.43..0.99 rows=3 width=4) (actual time=0.005..0.005 rows=1 loops=270538)'
'                                      Output: t22.id_table1, t22.elementcode'
'                                      Index Cond: (t22.id_table1 = t2.id_table1)'
'                                      Filter: (t22.elementcode <> ALL ('{1,13,17}'::integer[]))'
'                                      Rows Removed by Filter: 0'
'                                      Heap Fetches: 88960'
'                                      Buffers: shared hit=942069 read=232406'
'                                      Worker 0: actual time=0.005..0.005 rows=1 loops=68359'
'                                        Buffers: shared hit=237700 read=58872'
'                                      Worker 1: actual time=0.005..0.005 rows=1 loops=69505'
'                                        Buffers: shared hit=242120 read=59702'
'                                      Worker 2: actual time=0.005..0.005 rows=1 loops=66294'
'                                        Buffers: shared hit=230888 read=56843'
'        ->  Index Scan using table1_id_idx on public.table1 t1  (cost=0.42..8.44 rows=1 width=16) (actual time=0.016..0.017 rows=1 loops=5)'
'              Output: t1.id, t1.description'
'              Index Cond: (t1.id = t2.id_table1)'
'              Buffers: shared hit=4 read=16'
'  ->  Function Scan on pg_catalog.unnest ut2  (cost=0.00..1.00 rows=100 width=12) (actual time=0.004..0.005 rows=3 loops=5)'
'        Output: ut2.elementcode, ut2.qty, ut2.id_table1'
'        Function Call: unnest((array_agg(t2.*)))'
'Planning time: 0.492 ms'
'Execution time: 481.110 ms'