Row correction hint that works for regular JOIN doesn't work for LEFT JOIN

[wstrzalka]

Here is simplified example:

CREATE TABLE a (x INT4);
CREATE TABLE b (x INT4);
INSERT INTO a SELECT generate_series(1, 1000);
INSERT INTO b SELECT generate_series(1, 100);

1. So lets run vanilla query with regular JOIN first:

EXPLAIN
SELECT * FROM a
    JOIN b USING (x);

output is

INFO:  pg_hint_plan[qno=0xd]: planner: no valid hint
QUERY PLAN
---------------------------------------------------------------
 Hash Join  (cost=3.25..23.00 rows=100 width=4)
   Hash Cond: (a.x = b.x)
   ->  Seq Scan on a  (cost=0.00..15.00 rows=1000 width=4)
   ->  Hash  (cost=2.00..2.00 rows=100 width=4)
         ->  Seq Scan on b  (cost=0.00..2.00 rows=100 width=4)

2. Now let's apply some hint for same query

EXPLAIN
/*+ Rows(a b #100000) */
SELECT * FROM a
  JOIN b USING (x);

and the hint is properly applied

INFO:  pg_hint_plan[qno=0xe]: HintStateDump: {used hints:Rows(a b #100000)}, {not used hints:(none)}, {duplicate hints:(none)}, {error hints:(none)}
                          QUERY PLAN
---------------------------------------------------------------
 Hash Join  (cost=3.25..23.00 rows=100000 width=4)
   Hash Cond: (a.x = b.x)
   ->  Seq Scan on a  (cost=0.00..15.00 rows=1000 width=4)
   ->  Hash  (cost=2.00..2.00 rows=100 width=4)
         ->  Seq Scan on b  (cost=0.00..2.00 rows=100 width=4)

3. Let's switch to LEFT JOIN - vanilla query

EXPLAIN
SELECT * FROM a
  LEFT JOIN b USING (x);

produces

INFO:  pg_hint_plan[qno=0x10]: planner: no valid hint
                          QUERY PLAN
---------------------------------------------------------------
 Hash Left Join  (cost=3.25..23.00 rows=1000 width=4)
   Hash Cond: (a.x = b.x)
   ->  Seq Scan on a  (cost=0.00..15.00 rows=1000 width=4)
   ->  Hash  (cost=2.00..2.00 rows=100 width=4)
         ->  Seq Scan on b  (cost=0.00..2.00 rows=100 width=4)

4. And now let's try try to apply a hint for this LEFT JOIN:

EXPLAIN
/*+ Rows(a b #100000) */
SELECT * FROM a
  LEFT JOIN b USING (x);

but the output is dissapointing - hint was not applied:

INFO:  pg_hint_plan[qno=0xf]: HintStateDump: {used hints:(none)}, {not used hints:Rows(a b #100000)}, {duplicate hints:(none)}, {error hints:(none)}
                          QUERY PLAN
---------------------------------------------------------------
 Hash Left Join  (cost=3.25..23.00 rows=1000 width=4)
   Hash Cond: (a.x = b.x)
   ->  Seq Scan on a  (cost=0.00..15.00 rows=1000 width=4)
   ->  Hash  (cost=2.00..2.00 rows=100 width=4)
         ->  Seq Scan on b  (cost=0.00..2.00 rows=100 width=4)

[michaelpq]

It seems to me that this is mostly because nobody has written the code to support this case. While we do have some code for normal JOIN cases with row values adjusted through adjust_rows(), we would need an equivalent for other types of joins like this one. I am not sure how invasive it would be, but feel free to submit a patch if you are interested in this case, or find somebody to do that.