0002-Few-more-tests-and-adjustments-v2.patch
application/octet-stream
Filename: 0002-Few-more-tests-and-adjustments-v2.patch
Type: application/octet-stream
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v2-0002
Subject: Few more tests and adjustments v2
| File | + | − |
|---|---|---|
| src/test/regress/expected/partition_join.out | 169 | 40 |
| src/test/regress/sql/partition_join.sql | 30 | 19 |
From 22b1ff214ccec331d1e5d512ccd7e647db43f3b6 Mon Sep 17 00:00:00 2001
From: Amul Sul <amul.sul@enterprisedb.com>
Date: Tue, 5 Nov 2019 07:26:18 -0500
Subject: [PATCH 2/2] Few more tests and adjustments v2
---
src/test/regress/expected/partition_join.out | 209 +++++++++++++++----
src/test/regress/sql/partition_join.sql | 49 +++--
2 files changed, 199 insertions(+), 59 deletions(-)
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 618862ce87f..fa9e0872cbf 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1710,15 +1710,11 @@ RESET enable_hashjoin;
RESET enable_nestloop;
-- test default partition behavior for range, partition-wise join is not
-- possible since more than one partition on one side matches default partition
--- on the other side. Default partition from prt1 matches default partition and
--- prt2_p4 from prt2 and default partition from prt2 matches default partition
--- and prt1_p0 from prt1
+-- on the other side. Default partition from prt1 matches prt2_p3 and
+-- prt2_p4 partition from prt2.
ALTER TABLE prt1 DETACH PARTITION prt1_p3;
ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
ANALYZE prt1;
-ALTER TABLE prt2 DETACH PARTITION prt2_p3;
-ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
-ANALYZE prt2;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
QUERY PLAN
@@ -1731,8 +1727,8 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
-> Seq Scan on prt2_p0 t2
-> Seq Scan on prt2_p1 t2_1
-> Seq Scan on prt2_p2 t2_2
- -> Seq Scan on prt2_p4 t2_3
- -> Seq Scan on prt2_p3 t2_4
+ -> Seq Scan on prt2_p3 t2_3
+ -> Seq Scan on prt2_p4 t2_4
-> Hash
-> Append
-> Seq Scan on prt1_p0 t1
@@ -1747,51 +1743,46 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
Filter: (b = 0)
(22 rows)
--- partition-wise join should be possible when we drop the first and last
--- partitions from both sides
-ALTER TABLE prt1 DETACH PARTITION prt1_p0;
-ALTER TABLE prt1 DETACH PARTITION prt1_p4;
-ANALYZE prt1;
-ALTER TABLE prt2 DETACH PARTITION prt2_p0;
+-- partition-wise join should be possible when we drop the prt2_p4 from prt2.
ALTER TABLE prt2 DETACH PARTITION prt2_p4;
ANALYZE prt2;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
-> Hash Join
Hash Cond: (t2.b = t1.a)
- -> Seq Scan on prt2_p1 t2
+ -> Seq Scan on prt2_p0 t2
-> Hash
- -> Seq Scan on prt1_p1 t1
+ -> Seq Scan on prt1_p0 t1
Filter: (b = 0)
-> Hash Join
Hash Cond: (t2_1.b = t1_1.a)
- -> Seq Scan on prt2_p2 t2_1
+ -> Seq Scan on prt2_p1 t2_1
-> Hash
- -> Seq Scan on prt1_p2 t1_1
+ -> Seq Scan on prt1_p1 t1_1
Filter: (b = 0)
-> Hash Join
Hash Cond: (t2_2.b = t1_2.a)
- -> Seq Scan on prt2_p3 t2_2
+ -> Seq Scan on prt2_p2 t2_2
-> Hash
- -> Seq Scan on prt1_p3 t1_2
+ -> Seq Scan on prt1_p2 t1_2
Filter: (b = 0)
-(21 rows)
+ -> Nested Loop
+ -> Seq Scan on prt1_p3 t1_3
+ Filter: (b = 0)
+ -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
+ Index Cond: (b = t1_3.a)
+(26 rows)
-- restore the partitioned tables for rest of the tests
-ALTER TABLE prt1 ATTACH PARTITION prt1_p0 FOR VALUES FROM (MINVALUE) TO (0);
-ALTER TABLE prt1 ATTACH PARTITION prt1_p4 FOR VALUES FROM (600) TO (800);
ALTER TABLE prt1 DETACH PARTITION prt1_p3;
ALTER TABLE prt1 ATTACH PARTITION prt1_p3 FOR VALUES FROM (500) TO (600);
ANALYZE prt1;
-ALTER TABLE prt2 ATTACH PARTITION prt2_p0 FOR VALUES FROM (-250) TO (0);
ALTER TABLE prt2 ATTACH PARTITION prt2_p4 FOR VALUES FROM (600) TO (MAXVALUE);
-ALTER TABLE prt2 DETACH PARTITION prt2_p3;
-ALTER TABLE prt2 ATTACH PARTITION prt2_p3 FOR VALUES FROM (500) TO (600);
ANALYZE prt2;
-- Add an extra partition to prt2 , Partition-wise join is possible with
-- extra partitions on inner side are allowed
@@ -4877,12 +4868,14 @@ DROP TABLE plt2;
CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c);
CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN ('0000', '0002');
CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0003', '0004');
-INSERT INTO plt1 SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (0, 2, 3, 4);
+CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN ('0006', '0007');
+INSERT INTO plt1 SELECT i, i, to_char(i % 8, 'FM0000') FROM generate_series(0, 24) i WHERE i % 8 IN (0, 2, 3, 4, 6, 7);
ANALYZE plt1;
CREATE TABLE plt2 (a int, b int, c text) PARTITION BY LIST(c);
CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN ('0001', '0002');
CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0003', '0004');
-INSERT INTO plt2 SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (1, 2, 3, 4);
+CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0005', '0007');
+INSERT INTO plt2 SELECT i, i, to_char(i % 8, 'FM0000') FROM generate_series(0, 24) i WHERE i % 8 IN (1, 2, 3, 4, 5, 7);
ANALYZE plt2;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
@@ -4903,16 +4896,91 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON (t1.c = t2.c) WH
-> Seq Scan on plt1_p2 t1_1
-> Hash
-> Seq Scan on plt2_p2 t2_1
-(15 rows)
+ -> Hash Full Join
+ Hash Cond: (t1_2.c = t2_2.c)
+ Filter: (((COALESCE(t1_2.a, 0) % 5) <> 3) AND ((COALESCE(t1_2.a, 0) % 5) <> 4))
+ -> Seq Scan on plt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on plt2_p3 t2_2
+(21 rows)
SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
a | c | a | c
----+------+----+------
0 | 0000 | |
- 5 | 0000 | |
- 10 | 0000 | |
- 15 | 0000 | |
- 20 | 0000 | |
+ 16 | 0000 | |
+ 2 | 0002 | 2 | 0002
+ 2 | 0002 | 10 | 0002
+ 2 | 0002 | 18 | 0002
+ 10 | 0002 | 2 | 0002
+ 10 | 0002 | 10 | 0002
+ 10 | 0002 | 18 | 0002
+ 11 | 0003 | 3 | 0003
+ 11 | 0003 | 11 | 0003
+ 11 | 0003 | 19 | 0003
+ 12 | 0004 | 4 | 0004
+ 12 | 0004 | 12 | 0004
+ 12 | 0004 | 20 | 0004
+ 20 | 0004 | 4 | 0004
+ 20 | 0004 | 12 | 0004
+ 20 | 0004 | 20 | 0004
+ 6 | 0006 | |
+ 22 | 0006 | |
+ 7 | 0007 | 7 | 0007
+ 7 | 0007 | 15 | 0007
+ 7 | 0007 | 23 | 0007
+ 15 | 0007 | 7 | 0007
+ 15 | 0007 | 15 | 0007
+ 15 | 0007 | 23 | 0007
+ | | 1 | 0001
+ | | 5 | 0005
+ | | 9 | 0001
+ | | 13 | 0005
+ | | 17 | 0001
+ | | 21 | 0005
+(31 rows)
+
+DROP TABLE plt1;
+DROP TABLE plt2;
+CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c);
+CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN (NULL, '0001', '0002');
+CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0003', '0004');
+INSERT INTO plt1 SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (1, 2, 3, 4);
+ANALYZE plt1;
+CREATE TABLE plt2 (a int, b int, c text) PARTITION BY LIST(c);
+CREATE TABLE plt2_p1 PARTITION OF plt2 DEFAULT;
+CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0003', '0004');
+INSERT INTO plt2 SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (0, 2, 3, 4);
+ANALYZE plt2;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1.c, t1.a, t2_1.a
+ -> Append
+ -> Hash Full Join
+ Hash Cond: (t1.c = t2_1.c)
+ Filter: (((COALESCE(t1.a, 0) % 5) <> 3) AND ((COALESCE(t1.a, 0) % 5) <> 4))
+ -> Seq Scan on plt1_p1 t1
+ -> Hash
+ -> Seq Scan on plt2_p1 t2_1
+ -> Hash Full Join
+ Hash Cond: (t1_1.c = t2.c)
+ Filter: (((COALESCE(t1_1.a, 0) % 5) <> 3) AND ((COALESCE(t1_1.a, 0) % 5) <> 4))
+ -> Seq Scan on plt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on plt2_p2 t2
+(15 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
+ a | c | a | c
+----+------+----+------
+ 1 | 0001 | |
+ 6 | 0001 | |
+ 11 | 0001 | |
+ 16 | 0001 | |
+ 21 | 0001 | |
2 | 0002 | 2 | 0002
2 | 0002 | 7 | 0002
2 | 0002 | 12 | 0002
@@ -4938,11 +5006,72 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON (t1.c = t2.c) WH
22 | 0002 | 12 | 0002
22 | 0002 | 17 | 0002
22 | 0002 | 22 | 0002
- | | 1 | 0001
- | | 6 | 0001
- | | 11 | 0001
- | | 16 | 0001
- | | 21 | 0001
+ | | 0 | 0000
+ | | 5 | 0000
+ | | 10 | 0000
+ | | 15 | 0000
+ | | 20 | 0000
+(35 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt2 t2 FULL JOIN plt1 t1 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1.c, t1.a, t2_1.a
+ -> Append
+ -> Hash Full Join
+ Hash Cond: (t2_1.c = t1.c)
+ Filter: (((COALESCE(t1.a, 0) % 5) <> 3) AND ((COALESCE(t1.a, 0) % 5) <> 4))
+ -> Seq Scan on plt2_p1 t2_1
+ -> Hash
+ -> Seq Scan on plt1_p1 t1
+ -> Hash Full Join
+ Hash Cond: (t2.c = t1_1.c)
+ Filter: (((COALESCE(t1_1.a, 0) % 5) <> 3) AND ((COALESCE(t1_1.a, 0) % 5) <> 4))
+ -> Seq Scan on plt2_p2 t2
+ -> Hash
+ -> Seq Scan on plt1_p2 t1_1
+(15 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt2 t2 FULL JOIN plt1 t1 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
+ a | c | a | c
+----+------+----+------
+ 1 | 0001 | |
+ 6 | 0001 | |
+ 11 | 0001 | |
+ 16 | 0001 | |
+ 21 | 0001 | |
+ 2 | 0002 | 2 | 0002
+ 2 | 0002 | 7 | 0002
+ 2 | 0002 | 12 | 0002
+ 2 | 0002 | 17 | 0002
+ 2 | 0002 | 22 | 0002
+ 7 | 0002 | 2 | 0002
+ 7 | 0002 | 7 | 0002
+ 7 | 0002 | 12 | 0002
+ 7 | 0002 | 17 | 0002
+ 7 | 0002 | 22 | 0002
+ 12 | 0002 | 2 | 0002
+ 12 | 0002 | 7 | 0002
+ 12 | 0002 | 12 | 0002
+ 12 | 0002 | 17 | 0002
+ 12 | 0002 | 22 | 0002
+ 17 | 0002 | 2 | 0002
+ 17 | 0002 | 7 | 0002
+ 17 | 0002 | 12 | 0002
+ 17 | 0002 | 17 | 0002
+ 17 | 0002 | 22 | 0002
+ 22 | 0002 | 2 | 0002
+ 22 | 0002 | 7 | 0002
+ 22 | 0002 | 12 | 0002
+ 22 | 0002 | 17 | 0002
+ 22 | 0002 | 22 | 0002
+ | | 0 | 0000
+ | | 5 | 0000
+ | | 10 | 0000
+ | | 15 | 0000
+ | | 20 | 0000
(35 rows)
DROP TABLE plt1;
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index b7f53388d22..a54ef5ccada 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -221,39 +221,25 @@ RESET enable_nestloop;
-- test default partition behavior for range, partition-wise join is not
-- possible since more than one partition on one side matches default partition
--- on the other side. Default partition from prt1 matches default partition and
--- prt2_p4 from prt2 and default partition from prt2 matches default partition
--- and prt1_p0 from prt1
+-- on the other side. Default partition from prt1 matches prt2_p3 and
+-- prt2_p4 partition from prt2.
ALTER TABLE prt1 DETACH PARTITION prt1_p3;
ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
ANALYZE prt1;
-ALTER TABLE prt2 DETACH PARTITION prt2_p3;
-ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
-ANALYZE prt2;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
--- partition-wise join should be possible when we drop the first and last
--- partitions from both sides
-ALTER TABLE prt1 DETACH PARTITION prt1_p0;
-ALTER TABLE prt1 DETACH PARTITION prt1_p4;
-ANALYZE prt1;
-ALTER TABLE prt2 DETACH PARTITION prt2_p0;
+-- partition-wise join should be possible when we drop the prt2_p4 from prt2.
ALTER TABLE prt2 DETACH PARTITION prt2_p4;
ANALYZE prt2;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
-- restore the partitioned tables for rest of the tests
-ALTER TABLE prt1 ATTACH PARTITION prt1_p0 FOR VALUES FROM (MINVALUE) TO (0);
-ALTER TABLE prt1 ATTACH PARTITION prt1_p4 FOR VALUES FROM (600) TO (800);
ALTER TABLE prt1 DETACH PARTITION prt1_p3;
ALTER TABLE prt1 ATTACH PARTITION prt1_p3 FOR VALUES FROM (500) TO (600);
ANALYZE prt1;
-ALTER TABLE prt2 ATTACH PARTITION prt2_p0 FOR VALUES FROM (-250) TO (0);
ALTER TABLE prt2 ATTACH PARTITION prt2_p4 FOR VALUES FROM (600) TO (MAXVALUE);
-ALTER TABLE prt2 DETACH PARTITION prt2_p3;
-ALTER TABLE prt2 ATTACH PARTITION prt2_p3 FOR VALUES FROM (500) TO (600);
ANALYZE prt2;
-- Add an extra partition to prt2 , Partition-wise join is possible with
@@ -822,19 +808,44 @@ DROP TABLE plt2;
CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c);
CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN ('0000', '0002');
CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0003', '0004');
-INSERT INTO plt1 SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (0, 2, 3, 4);
+CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN ('0006', '0007');
+INSERT INTO plt1 SELECT i, i, to_char(i % 8, 'FM0000') FROM generate_series(0, 24) i WHERE i % 8 IN (0, 2, 3, 4, 6, 7);
ANALYZE plt1;
CREATE TABLE plt2 (a int, b int, c text) PARTITION BY LIST(c);
CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN ('0001', '0002');
CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0003', '0004');
-INSERT INTO plt2 SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (1, 2, 3, 4);
+CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0005', '0007');
+INSERT INTO plt2 SELECT i, i, to_char(i % 8, 'FM0000') FROM generate_series(0, 24) i WHERE i % 8 IN (1, 2, 3, 4, 5, 7);
+ANALYZE plt2;
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
+
+DROP TABLE plt1;
+DROP TABLE plt2;
+
+CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c);
+CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN (NULL, '0001', '0002');
+CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0003', '0004');
+INSERT INTO plt1 SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (1, 2, 3, 4);
+ANALYZE plt1;
+
+CREATE TABLE plt2 (a int, b int, c text) PARTITION BY LIST(c);
+CREATE TABLE plt2_p1 PARTITION OF plt2 DEFAULT;
+CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0003', '0004');
+INSERT INTO plt2 SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (0, 2, 3, 4);
ANALYZE plt2;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
SELECT t1.a, t1.c, t2.a, t2.c FROM plt1 t1 FULL JOIN plt2 t2 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt2 t2 FULL JOIN plt1 t1 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
+SELECT t1.a, t1.c, t2.a, t2.c FROM plt2 t2 FULL JOIN plt1 t1 ON (t1.c = t2.c) WHERE COALESCE(t1.a, 0) % 5 != 3 AND COALESCE(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a;
+
DROP TABLE plt1;
DROP TABLE plt2;
--
2.18.0