select p.* from person p 
where 
exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='SMS')
and
exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='PUSH');

--создаем 1.000.000 persons
mboguk=# create database test;
CREATE DATABASE

mboguk=# \c test
You are now connected to database "test" as user "mboguk".

test=# create table person as select id from generate_series(1,1000000) as g(id);
SELECT 1000000

test=# alter table person add primary key (id);
ALTER TABLE

test=# vacuum freeze analyze person;
VACUUM


--создаем person_channel с типами SMS, PUSH, POSTAL DOVE c распределением вида 1:1:0.001
test=# create table person_channel as select person_id, case when random()>0.5 then 'SMS' when random()<0.001 then 'POSTAL DOVE' else 'PUSH' end as channel_type from generate_series(1,1000000) as g(person_id);
SELECT 1000000

test=# insert into person_channel select pc1.* FROM (select person_id, case when random()>0.5 then 'SMS' when random()<0.001 then 'POSTAL DOVE' else 'PUSH' end as channel_type from generate_series(1,1000000) as g(person_id)) as pc1 where not exists (select from person_channel pc2 where pc2.person_id=pc1.person_id and pc2.channel_type=pc1.channel_type);
INSERT 0 500390

test=# create UNIQUE index person_channel_person_id_channel_type on person_channel(person_id, channel_type);
CREATE INDEX

test=# create index person_channel_channel_type on person_channel(channel_type);
CREATE INDEX

test=# vacuum freeze ANALYZE person_channel;
VACUUM

test=# select count(*), channel_type from person_channel group by 2;
 count  | channel_type 
--------+--------------
   1034 | POSTAL DOVE
 749497 | PUSH
 749859 | SMS

test=# explain analyze select * from person p where
exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='SMS')
and exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='PUSH')
and exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='POSTAL DOVE')
;
                                                                                  QUERY PLAN                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=13.85..7725.00 rows=590 width=4) (actual time=25.854..25.856 rows=0 loops=1)
   ->  Nested Loop  (cost=13.42..7358.65 rows=784 width=12) (actual time=0.829..22.298 rows=510 loops=1)
         ->  Nested Loop  (cost=12.99..6868.01 rows=1050 width=8) (actual time=0.811..12.571 rows=1034 loops=1)
               ->  Bitmap Heap Scan on person_channel pc_2  (cost=12.56..2875.39 rows=1050 width=4) (actual time=0.787..3.930 rows=1034 loops=1)
                     Recheck Cond: (channel_type = 'POSTAL DOVE'::text)
                     Heap Blocks: exact=953
                     ->  Bitmap Index Scan on person_channel_channel_type  (cost=0.00..12.30 rows=1050 width=0) (actual time=0.398..0.398 rows=1034 loops=1)
                           Index Cond: (channel_type = 'POSTAL DOVE'::text)
               ->  Index Only Scan using person_pkey on person p  (cost=0.42..3.80 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1034)
                     Index Cond: (id = pc_2.person_id)
                     Heap Fetches: 0
         ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc_1  (cost=0.43..0.47 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=1034)
               Index Cond: ((person_id = p.id) AND (channel_type = 'PUSH'::text))
               Heap Fetches: 0
   ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc  (cost=0.43..0.47 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=510)
         Index Cond: ((person_id = p.id) AND (channel_type = 'SMS'::text))
         Heap Fetches: 0
 Planning Time: 3.106 ms
 Execution Time: 25.939 ms

explain analyze select * from person p where 
exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='SMS')
and exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='PUSH')
--and exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='POSTAL DOVE')
;
                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=74774.24..113292.60 rows=561997 width=4) (actual time=417.767..1089.731 rows=499356 loops=1)
   Hash Cond: (p.id = pc.person_id)
   ->  Hash Join  (cost=37273.93..65055.94 rows=746794 width=8) (actual time=188.063..608.533 rows=749497 loops=1)
         Hash Cond: (p.id = pc_1.person_id)
         ->  Seq Scan on person p  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.019..61.047 rows=1000000 loops=1)
         ->  Hash  (cost=25021.01..25021.01 rows=746794 width=4) (actual time=187.852..187.853 rows=749497 loops=1)
               Buckets: 131072  Batches: 16  Memory Usage: 2676kB
               ->  Bitmap Heap Scan on person_channel pc_1  (cost=8324.08..25021.01 rows=746794 width=4) (actual time=17.158..98.042 rows=749497 loops=1)
                     Recheck Cond: (channel_type = 'PUSH'::text)
                     Heap Blocks: exact=7362
                     ->  Bitmap Index Scan on person_channel_channel_type  (cost=0.00..8137.38 rows=746794 width=0) (actual time=16.289..16.290 rows=749497 loops=1)
                           Index Cond: (channel_type = 'PUSH'::text)
   ->  Hash  (cost=25153.48..25153.48 rows=752546 width=4) (actual time=229.523..229.524 rows=749859 loops=1)
         Buckets: 131072  Batches: 16  Memory Usage: 2677kB
         ->  Bitmap Heap Scan on person_channel pc  (cost=8384.66..25153.48 rows=752546 width=4) (actual time=47.556..133.339 rows=749859 loops=1)
               Recheck Cond: (channel_type = 'SMS'::text)
               Heap Blocks: exact=7362
               ->  Bitmap Index Scan on person_channel_channel_type  (cost=0.00..8196.52 rows=752546 width=0) (actual time=36.843..36.844 rows=749859 loops=1)
                     Index Cond: (channel_type = 'SMS'::text)
 Planning Time: 1.277 ms
 JIT:
   Functions: 22
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 4.455 ms, Inlining 0.000 ms, Optimization 0.370 ms, Emission 9.141 ms, Total 13.967 ms
 Execution Time: 1106.620 ms
(25 rows)

test=# drop table person_channel;
DROP TABLE
test=# create table person_channel as select person_id, case when random()>0.5 then 'SMS' when random()<0.001 then 'POSTAL DOVE' else 'PUSH' end as channel_type from generate_series(1,1000000) as g(person_id);
SELECT 1000000
test=# insert into person_channel select pc1.* FROM (select person_id, case when random()>0.5 then 'SMS' when random()<0.001 then 'POSTAL DOVE' else 'PUSH' end as channel_type from generate_series(1,1000000) as g(person_id)) as pc1;
INSERT 0 1000000
test=# create index person_channel_person_id_channel_type on person_channel(person_id, channel_type);
CREATE INDEX

test=# create index person_channel_channel_type on person_channel(channel_type);
CREATE INDEX

test=# vacuum freeze ANALYZE person_channel;
VACUUM

--куча дубликатов
test=# select count(*), channel_type from person_channel group by 2;
 count  | channel_type 
--------+--------------
    953 | POSTAL DOVE
 999390 | PUSH
 999657 | SMS

test=# explain analyze select * from person p where exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='SMS')
and
exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='PUSH')
and exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='POSTAL DOVE')
;

                                                                                   QUERY PLAN                                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=2700.45..7174.53 rows=865 width=4) (actual time=22.187..22.190 rows=0 loops=1)
   ->  Nested Loop Semi Join  (cost=2700.03..6747.99 rows=886 width=12) (actual time=3.007..19.106 rows=477 loops=1)
         ->  Nested Loop  (cost=2699.60..6309.92 rows=910 width=8) (actual time=2.983..10.912 rows=953 loops=1)
               ->  HashAggregate  (cost=2699.17..2708.50 rows=933 width=4) (actual time=2.950..3.506 rows=953 loops=1)
                     Group Key: pc_2.person_id
                     Batches: 1  Memory Usage: 129kB
                     ->  Index Scan using person_channel_channel_type on person_channel pc_2  (cost=0.43..2696.84 rows=933 width=4) (actual time=0.039..2.053 rows=953 loops=1)
                           Index Cond: (channel_type = 'POSTAL DOVE'::text)
               ->  Index Only Scan using person_pkey on person p  (cost=0.42..3.86 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=953)
                     Index Cond: (id = pc_2.person_id)
                     Heap Fetches: 0
         ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc  (cost=0.43..0.47 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=953)
               Index Cond: ((person_id = p.id) AND (channel_type = 'SMS'::text))
               Heap Fetches: 0
   ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc_1  (cost=0.43..0.47 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=477)
         Index Cond: ((person_id = p.id) AND (channel_type = 'PUSH'::text))
         Heap Fetches: 0
 Planning Time: 1.697 ms
 Execution Time: 22.298 ms
(19 rows)

test=# explain analyze SELECT  * from person p where p.id IN (SELECT person_id FROM person_channel WHERE channel_type IN ('SMS',  'PUSH',  'POSTAL DOVE') GROUP BY person_id HAVING count(distinct channel_type)>=3);
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.85..120641.68 rows=341747 width=4) (actual time=1188.548..1188.550 rows=0 loops=1)
   Merge Cond: (p.id = person_channel.person_id)
   ->  Index Only Scan using person_pkey on person p  (cost=0.42..25980.42 rows=1000000 width=4) (actual time=0.105..0.106 rows=1 loops=1)
         Heap Fetches: 0
   ->  GroupAggregate  (cost=0.43..84471.95 rows=341747 width=4) (actual time=1177.114..1177.115 rows=0 loops=1)
         Group Key: person_channel.person_id
         Filter: (count(DISTINCT person_channel.channel_type) >= 3)
         Rows Removed by Filter: 1000000
         ->  Index Only Scan using person_channel_person_id_channel_type on person_channel  (cost=0.43..61656.43 rows=2000000 width=8) (actual time=0.088..304.581 rows=2000000 loops=1)
               Filter: (channel_type = ANY ('{SMS,PUSH,"POSTAL DOVE"}'::text[]))
               Heap Fetches: 0
 Planning Time: 0.474 ms
 JIT:
   Functions: 8
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 3.186 ms, Inlining 0.000 ms, Optimization 0.577 ms, Emission 10.271 ms, Total 14.034 ms
 Execution Time: 1191.897 ms

test=# explain analyze SELECT  * from person p where p.id IN (SELECT person_id FROM person_channel WHERE channel_type IN ('SMS',  'PUSH') GROUP BY person_id HAVING count(distinct channel_type)>=2);
                                                                                       QUERY PLAN                                                                                        
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.85..118137.02 rows=341747 width=4) (actual time=10.267..1458.642 rows=500030 loops=1)
   Merge Cond: (p.id = person_channel.person_id)
   ->  Index Only Scan using person_pkey on person p  (cost=0.42..25980.42 rows=1000000 width=4) (actual time=0.058..91.289 rows=1000000 loops=1)
         Heap Fetches: 0
   ->  GroupAggregate  (cost=0.43..81967.29 rows=341747 width=4) (actual time=0.123..1241.724 rows=500030 loops=1)
         Group Key: person_channel.person_id
         Filter: (count(DISTINCT person_channel.channel_type) >= 2)
         Rows Removed by Filter: 499970
         ->  Index Only Scan using person_channel_person_id_channel_type on person_channel  (cost=0.43..59156.43 rows=1999067 width=8) (actual time=0.071..313.379 rows=1999047 loops=1)
               Filter: (channel_type = ANY ('{SMS,PUSH}'::text[]))
               Rows Removed by Filter: 953
               Heap Fetches: 0
 Planning Time: 0.402 ms
 JIT:
   Functions: 8
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 2.104 ms, Inlining 0.000 ms, Optimization 0.373 ms, Emission 9.337 ms, Total 11.813 ms
 Execution Time: 1474.000 ms

vs

test=# explain analyze select * from person p where exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='SMS')
and
exists (select from person_channel pc where p.id = pc.person_id and pc.channel_type='PUSH')
;                                                                                                     
                                                                                        QUERY PLAN                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Semi Join  (cost=4.88..153455.42 rows=950476 width=4) (actual time=7.121..640.741 rows=500030 loops=1)
   Merge Cond: (p.id = pc_1.person_id)
   ->  Merge Semi Join  (cost=1.71..89851.05 rows=973689 width=8) (actual time=7.061..386.027 rows=750082 loops=1)
         Merge Cond: (p.id = pc.person_id)
         ->  Index Only Scan using person_pkey on person p  (cost=0.42..25980.42 rows=1000000 width=4) (actual time=0.055..85.834 rows=1000000 loops=1)
               Heap Fetches: 0
         ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc  (cost=0.43..49139.10 rows=998267 width=4) (actual time=0.098..116.771 rows=999657 loops=1)
               Index Cond: (channel_type = 'SMS'::text)
               Heap Fetches: 0
   ->  Index Only Scan using person_channel_person_id_channel_type on person_channel pc_1  (cost=0.43..49164.43 rows=1000800 width=4) (actual time=0.045..114.944 rows=999390 loops=1)
         Index Cond: (channel_type = 'PUSH'::text)
         Heap Fetches: 0
 Planning Time: 0.974 ms
 JIT:
   Functions: 8
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.916 ms, Inlining 0.000 ms, Optimization 0.340 ms, Emission 6.218 ms, Total 8.474 ms
 Execution Time: 655.357 ms
(18 rows)