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
)