CREATE TABLE sg (
id bigint NOT NULL,
sc_fk bigint,
geo_id bigint,
sl smallint NOT NULL,
a date NOT NULL,
o boolean NOT NULL
)
PARTITION BY RANGE (o, sl, a);

CREATE TABLE sg_19_01_d PARTITION OF sg FOR VALUES FROM (false, '5', '2019-01-01') TO (false, '5', '2019-02-01');
CREATE TABLE sg_19_02_d PARTITION OF sg FOR VALUES FROM (false, '5', '2019-02-01') TO (false, '5', '2019-03-01');

CREATE TABLE sc (
id bigint,
a date NOT NULL,
sl smallint NOT NULL,
o boolean NOT NULL
)
PARTITION BY RANGE (o, sl, a);

CREATE TABLE sc_19_01_d PARTITION OF sc FOR VALUES FROM (false, '5', '2019-01-01') TO (false, '5', '2019-02-01');
CREATE TABLE sc_19_02_d PARTITION OF sc FOR VALUES FROM (false, '5', '2019-02-01') TO (false, '5', '2019-03-01');

INSERT INTO sg_19_01_d(id, sc_fk, geo_id, sl, a, o)
SELECT n, n, 0, 5, '2019-01-01', false
  FROM generate_series(1, 1000) AS g(n);

INSERT INTO sg_19_02_d(id, sc_fk, geo_id, sl, a, o)
SELECT n, n, 0, 5, '2019-02-01', false
  FROM generate_series(1, 1000) AS g(n);

INSERT INTO sc_19_01_d(id, a, sl, o)
SELECT n, '2019-01-01', 5, false
  FROM generate_series(1, 1000) AS g(n);

INSERT INTO sc_19_02_d(id, a, sl, o)
SELECT n, '2019-02-01', 5, false
  FROM generate_series(1, 1000) AS g(n);

ANALYZE sg_19_01_d;
ANALYZE sg_19_02_d;
ANALYZE sc_19_01_d;
ANALYZE sc_19_02_d;

--first
SET enable_partitionwise_join = false;
SET enable_partitionwise_aggregate = false;
SET enable_hashjoin = false;
EXPLAIN (VERBOSE, ANALYZE)
SELECT COUNT(*)
  FROM sc
 WHERE EXISTS (
       SELECT 1
         FROM sg
        WHERE sc.id = sg.sc_fk
          AND sc.a = sg.a
          AND sc.o = sg.o
          AND sc.sl = sg.sl
       );

--second
SET enable_partitionwise_join = true;
SET enable_partitionwise_aggregate = true;
--SET enable_hashjoin = false;
EXPLAIN (VERBOSE, ANALYZE)
SELECT COUNT(*)
  FROM sc
 WHERE EXISTS (
       SELECT 1
         FROM sg
        WHERE sc.id = sg.sc_fk
          AND sc.a = sg.a
          AND sc.o = sg.o
          AND sc.sl = sg.sl
       );

--third
--SET enable_partitionwise_join = true;
--SET enable_partitionwise_aggregate = true;
--SET enable_hashjoin = false;
SET enable_mergejoin = false;
EXPLAIN (VERBOSE, ANALYZE)
SELECT COUNT(*)
  FROM sc
 WHERE sl = 5
 and EXISTS (
       SELECT 1
         FROM sg
        WHERE sc.id = sg.sc_fk
          AND sc.a = sg.a
          AND sc.o = sg.o
          AND sc.sl = sg.sl
       )