--
---- test for scan for partitioned table
---- add by jiayanhua
---- case 1: partitioned table + partitioned table
--prepare
CREATE TABLE partition_scan_stu_info3(SN INT, NAME NAME)
PARTITION BY RANGE (SN)
(
PARTITION P1_partition_scan_stu_info3 VALUES LESS THAN(10),
PARTITION P2_partition_scan_stu_info3 VALUES LESS THAN(20),
PARTITION P3_partition_scan_stu_info3 VALUES LESS THAN(30),
PARTITION P4_partition_scan_stu_info3 VALUES LESS THAN(40)
);
CREATE TABLE partition_scan_stu_info4(SN INT, PHONE NAME)
PARTITION BY RANGE (SN)
(
PARTITION P1_partition_scan_stu_info4 VALUES LESS THAN(10),
PARTITION P2_partition_scan_stu_info4 VALUES LESS THAN(20),
PARTITION P3_partition_scan_stu_info4 VALUES LESS THAN(30),
PARTITION P4_partition_scan_stu_info4 VALUES LESS THAN(40)
);
CREATE UNIQUE INDEX index_on_partition_scan_stu_info3 ON partition_scan_stu_info3 (SN) LOCAL;
CREATE UNIQUE INDEX index_on_partition_scan_stu_info4 ON partition_scan_stu_info4 (SN) LOCAL;
INSERT INTO partition_scan_stu_info3 VALUES (1,'DFM');
INSERT INTO partition_scan_stu_info3 VALUES (11,'CHAO');
INSERT INTO partition_scan_stu_info3 VALUES (21,'ZJR');
INSERT INTO partition_scan_stu_info3 VALUES (31,'JYH');
INSERT INTO partition_scan_stu_info4 VALUES (1,'15478523126');
INSERT INTO partition_scan_stu_info4 VALUES (11,'15236997586');
INSERT INTO partition_scan_stu_info4 VALUES (21,'15936985364');
INSERT INTO partition_scan_stu_info4 VALUES (31,'15873285556');
SELECT * FROM partition_scan_stu_info3 ORDER BY 1;
SELECT * FROM partition_scan_stu_info4 ORDER BY 1;
--
---- nestloop + bitmapscan
--
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_indexonlyscan = OFF;
SET enable_bitmapscan = ON;
SET enable_tidscan = OFF;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = ON;
SET enable_mergejoin = OFF;
SET enable_hashjoin = OFF;
-- explain cross join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
---- nestloop + tidscan
--
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_indexonlyscan = OFF;
SET enable_bitmapscan = OFF;
SET enable_tidscan = ON;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = ON;
SET enable_mergejoin = OFF;
SET enable_hashjoin = OFF;
-- explain cross join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
----mergejoin + seqscan
--
SET enable_seqscan = ON;
SET enable_indexscan = OFF;
SET enable_indexonlyscan = OFF;
SET enable_bitmapscan = OFF;
SET enable_tidscan = OFF;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = OFF;
SET enable_mergejoin = ON;
SET enable_hashjoin = OFF;
-- explain cross join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
----mergejoin + indexscan
--
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_indexonlyscan = OFF;
SET enable_bitmapscan = OFF;
SET enable_tidscan = OFF;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = OFF;
SET enable_mergejoin = ON;
SET enable_hashjoin = OFF;
--
---- explain cross join
--
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
--mergejoin + indexonlyscan
--
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_indexonlyscan = ON;
SET enable_bitmapscan = OFF;
SET enable_tidscan = OFF;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = OFF;
SET enable_mergejoin = ON;
SET enable_hashjoin = OFF;
-- explain cross join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
---- mergejoin + bitmapscan
--
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_indexonlyscan = OFF;
SET enable_bitmapscan = ON;
SET enable_tidscan = OFF;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = OFF;
SET enable_mergejoin = ON;
SET enable_hashjoin = OFF;
-- explain cross join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
---- mergejoin + tidscan
--
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_indexonlyscan = OFF;
SET enable_bitmapscan = OFF;
SET enable_tidscan = ON;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = OFF;
SET enable_mergejoin = ON;
SET enable_hashjoin = OFF;
-- explain cross join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
---- all + seqscan
--
SET enable_seqscan = ON;
SET enable_indexscan = OFF;
SET enable_indexonlyscan = OFF;
SET enable_bitmapscan = OFF;
SET enable_tidscan = OFF;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = ON;
SET enable_mergejoin = ON;
SET enable_hashjoin = ON;
-- explain cross join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
---- all + indexscan
--
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_indexonlyscan = OFF;
SET enable_bitmapscan = OFF;
SET enable_tidscan = OFF;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = ON;
SET enable_mergejoin = ON;
SET enable_hashjoin = ON;
--
---- explain cross join
--
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
-- all + indexonlyscan
--
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_indexonlyscan = ON;
SET enable_bitmapscan = OFF;
SET enable_tidscan = OFF;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = ON;
SET enable_mergejoin = ON;
SET enable_hashjoin = ON;
-- explain cross join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
---- all + bitmapscan
--
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_indexonlyscan = OFF;
SET enable_bitmapscan = ON;
SET enable_tidscan = OFF;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = ON;
SET enable_mergejoin = ON;
SET enable_hashjoin = ON;
-- explain cross join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
---- all + tidscan
--
SET enable_seqscan = OFF;
SET enable_indexscan = OFF;
SET enable_indexonlyscan = OFF;
SET enable_bitmapscan = OFF;
SET enable_tidscan = ON;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = ON;
SET enable_mergejoin = ON;
SET enable_hashjoin = ON;
-- explain cross join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
--
---- all is open
--
SET enable_seqscan = ON;
SET enable_indexscan = ON;
SET enable_indexonlyscan = ON;
SET enable_bitmapscan = ON;
SET enable_tidscan = ON;
SET enable_sort = ON;
SET enable_material = OFF;
SET enable_nestloop = ON;
SET enable_mergejoin = ON;
SET enable_hashjoin = ON;
-- explain cross join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4;
-- explain inner join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain left outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain right outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain full outer join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- explain for no partition to join
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
EXPLAIN (COSTS OFF, NODES OFF) SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- rsult of cross join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 CROSS JOIN partition_scan_stu_info4 order by 1, 2;
-- rsult of inner joi
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 INNER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of left join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of right join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 RIGHT OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- rsult of FULL join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 FULL OUTER JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) ORDER BY partition_scan_stu_info3.SN, partition_scan_stu_info3.NAME, partition_scan_stu_info3.NAME;
-- result of no partition to join
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info3.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40;
SELECT partition_scan_stu_info3.NAME, partition_scan_stu_info4.PHONE FROM partition_scan_stu_info3 LEFT JOIN partition_scan_stu_info4 ON (partition_scan_stu_info3.SN = partition_scan_stu_info4.SN) WHERE partition_scan_stu_info4.SN > 40 AND partition_scan_stu_info3.SN > 40;
-- clean up
DROP TABLE partition_scan_stu_info3;
DROP TABLE partition_scan_stu_info4;