FDW changes the query plan based on optional key column(s)

[rajlearner17]

Case-1: When visibility is not optional key qual in github_my_repository table

explain select
      r.full_name as resource,
      case
        when b.allow_force_pushes_enabled = 'false' then 'ok'
        else 'alarm'
      end as status,
      r.full_name || ' default branch ' || b.name ||
        case
          when b.allow_force_pushes_enabled = 'false' then ' prevents force push.'
          when b.allow_force_pushes_enabled = 'true' then ' allows force push.'
          -- If not false or true, then null, which means no branch protection rule exists
          else ' is not protected.'
        end as reason,
      r.full_name
    from
      github_my_repository as r
      left join github_branch_protection as b on r.full_name = b.repository_full_name
    where
      visibility = 'public' and r.fork = false and (b.name = 'main' or b.name = 'master')
+---------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                    |
+---------------------------------------------------------------------------------------------------------------+
| Nested Loop  (cost=0.00..30003037600000.00 rows=5000000000 width=128)                                         |
|   ->  Foreign Scan on github_my_repository r  (cost=0.00..30000000000000.00 rows=1000000 width=300)           |
|         **Filter: ((NOT fork) AND (visibility = 'public'::text))**                                                |
|   ->  Foreign Scan on github_branch_protection b  (cost=0.00..3000.00 rows=10 width=300)                      |
|         Filter: ((r.full_name = repository_full_name) AND ((name = 'main'::text) OR (name = 'master'::text))) |
+---------------------------------------------------------------------------------------------------------------+
>

Case-2: When visibility is optional key qual in github_my_repository table

> explain select
      r.full_name as resource,
      case
        when b.allow_force_pushes_enabled = 'false' then 'ok'
        else 'alarm'
      end as status,
      r.full_name || ' default branch ' || b.name ||
        case
          when b.allow_force_pushes_enabled = 'false' then ' prevents force push.'
          when b.allow_force_pushes_enabled = 'true' then ' allows force push.'
          -- If not false or true, then null, which means no branch protection rule exists
          else ' is not protected.'
        end as reason,
      r.full_name
    from
      github_my_repository as r
      left join github_branch_protection as b on r.full_name = b.repository_full_name
    where
      visibility = 'public' and r.fork = false and (b.name = 'main' or b.name = 'master')
+-------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| Nested Loop  (cost=0.00..30000337510000.00 rows=5000000000 width=128)                                                                     |
|   ->  Foreign Scan on github_branch_protection b  (cost=0.00..30000000000000.00 rows=1000000 width=300)                                   
|         **Filter: ((name = 'main'::text) OR (name = 'master'::text))**                                                                        |
|   ->  Foreign Scan on github_my_repository r  (cost=0.00..300.00 rows=1 width=300)                                                        |
|         Filter: ((NOT fork) AND (visibility = 'public'::text) AND (visibility = 'public'::text) AND (full_name = b.repository_full_name)) |
+-------------------------------------------------------------------------------------------------------------------------------------------+
> 

The order of filter highlighted in **-** is changed.

[rajlearner17]

Some of the control fails due same reason
turbot/steampipe-mod-github-sherlock#14