-- Create two tables
CREATE TABLE table1(pk INT PRIMARY KEY) PARTITION BY HASH(pk);
CREATE TABLE table2(pk INT PRIMARY KEY) PARTITION BY HASH(pk);

-- Create 4 partitions for each of the two tables
CREATE TABLE table1_p1 PARTITION OF table1 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE table1_p2 PARTITION OF table1 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE table1_p3 PARTITION OF table1 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE table1_p4 PARTITION OF table1 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

CREATE TABLE table2_p1 PARTITION OF table2 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE table2_p2 PARTITION OF table2 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE table2_p3 PARTITION OF table2 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE table2_p4 PARTITION OF table2 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- INSERT data into both of the two tables.
INSERT INTO table1 SELECT generate_series(1, 8);
INSERT INTO table2 SELECT generate_series(1, 8);



------------------------------------------------------------------
QueryPlan for the query with IN clause
------------------------------------------------------------------

postgres=# EXPLAIN (COSTS OFF) SELECT * FROM table1 t1 INNER JOIN table2 t2
  ON (t1.pk = t2.pk)
  WHERE t1.pk IN (1,2);
                             QUERY PLAN
--------------------------------------------------------------------
 Nested Loop
   ->  Append
         ->  Bitmap Heap Scan on table1_p1 t1
               Recheck Cond: (pk = ANY ('{1,2}'::integer[]))
               ->  Bitmap Index Scan on table1_p1_pkey
                     Index Cond: (pk = ANY ('{1,2}'::integer[]))
         ->  Bitmap Heap Scan on table1_p3 t1_1
               Recheck Cond: (pk = ANY ('{1,2}'::integer[]))
               ->  Bitmap Index Scan on table1_p3_pkey
                     Index Cond: (pk = ANY ('{1,2}'::integer[]))
   ->  Append
         ->  Index Only Scan using table2_p1_pkey on table2_p1 t2
               Index Cond: (pk = t1.pk)
         ->  Index Only Scan using table2_p2_pkey on table2_p2 t2_1
               Index Cond: (pk = t1.pk)
         ->  Index Only Scan using table2_p3_pkey on table2_p3 t2_2
               Index Cond: (pk = t1.pk)
         ->  Index Only Scan using table2_p4_pkey on table2_p4 t2_3
               Index Cond: (pk = t1.pk)
(19 rows)




-------------------------------------------------------------------------------------------------------------------------
QueryPlan for the query with OR clause
-------------------------------------------------------------------------------------------------------------------------
postgres=# EXPLAIN (COSTS OFF) SELECT * FROM table1 t1 INNER JOIN table2 t2
  ON (t1.pk = t2.pk)
  WHERE t1.pk = 1 OR t1.pk = 2;
                             QUERY PLAN
--------------------------------------------------------------------
 Nested Loop
   ->  Append
         ->  Bitmap Heap Scan on table1_p1 t1
               Recheck Cond: ((pk = 1) OR (pk = 2))
               ->  BitmapOr
                     ->  Bitmap Index Scan on table1_p1_pkey
                           Index Cond: (pk = 1)
                     ->  Bitmap Index Scan on table1_p1_pkey
                           Index Cond: (pk = 2)
         ->  Bitmap Heap Scan on table1_p3 t1_1
               Recheck Cond: ((pk = 1) OR (pk = 2))
               ->  BitmapOr
                     ->  Bitmap Index Scan on table1_p3_pkey
                           Index Cond: (pk = 1)
                     ->  Bitmap Index Scan on table1_p3_pkey
                           Index Cond: (pk = 2)
   ->  Append
         ->  Index Only Scan using table2_p1_pkey on table2_p1 t2
               Index Cond: (pk = t1.pk)
         ->  Index Only Scan using table2_p2_pkey on table2_p2 t2_1
               Index Cond: (pk = t1.pk)
         ->  Index Only Scan using table2_p3_pkey on table2_p3 t2_2
               Index Cond: (pk = t1.pk)
         ->  Index Only Scan using table2_p4_pkey on table2_p4 t2_3
               Index Cond: (pk = t1.pk)
(25 rows)

------------------------------------------------------------------
QueryPlan for the query with just a filter
------------------------------------------------------------------
postgres=# EXPLAIN (COSTS OFF) SELECT * FROM table1 t1 INNER JOIN table2 t2
  ON (t1.pk = t2.pk)
  WHERE t1.pk = 1;
                            QUERY PLAN
------------------------------------------------------------------
 Nested Loop
   ->  Append
         ->  Index Only Scan using table1_p1_pkey on table1_p1 t1
               Index Cond: (pk = 1)
   ->  Append
         ->  Index Only Scan using table2_p1_pkey on table2_p1 t2
               Index Cond: (pk = 1)
(7 rows)