title: Postgres FDW join query on local and foreign table is slow with ORDER BY and JSONB data
body: <p>I have two tables the local table <code>debtors</code> and the foreign table <code>debtor_registry</code>. I'm using PostgreSQL v13.</p>
  <p>My problem here is whenever I try the following query, It takes <strong>14secs</strong> to get the 1000 records.</p>
  <pre><code>SELECT 
      debtors.id,
      debtors.name,
      debtor_registry.settings
  FROM debtors
      INNER JOIN debtor_registry ON debtor_registry.id = debtors.registry_uuid
  ORDER BY name LIMIT 1000 OFFSET 0;
  </code></pre>
  <p>I was surprised that whenever I removed the <code>ORDER BY</code> clause from the query, It become faster only took <strong>194ms</strong> for 1000 records.</p>
  <pre><code>SELECT 
      debtors.id,
      debtors.name,
      debtor_registry.settings
  FROM debtors
      INNER JOIN debtor_registry ON debtor_registry.id = debtors.registry_uuid
  LIMIT 1000 OFFSET 0;
  </code></pre>
  <p>Also, another case is if I remove the <code>settings</code> which is a JSONB field from the query, and retain the <code>ORDER BY</code> clause. It only took <strong>101ms</strong> to get 1000 records.</p>
  <pre><code>SELECT 
      debtors.id,
      debtors.name
  FROM debtors
      INNER JOIN debtor_registry ON debtor_registry.id = debtors.registry_uuid
  ORDER BY name LIMIT 1000 OFFSET 0;
  </code></pre>
  <p>I'm suspecting that It might be related to how much data I am trying to get.</p>
  <p>Here is the <code>EXPLAIN ANALYZE VERBOSE</code> result if the <code>settings</code> JSONB fields, <code>ORDER BY name</code> and <code>LIMIT 1000</code> are in the query:</p>
  <pre><code>Limit  (cost=114722.78..114725.28 rows=1000 width=57) (actual time=13712.125..14002.827 rows=1000 loops=1)
    Output: debtors.id, debtors.name, debtor_registry.settings
    -&gt;  Sort  (cost=114722.78..114725.63 rows=1140 width=57) (actual time=13703.171..13993.617 rows=1000 loops=1)
          Output: debtors.id, debtors.name, debtor_registry.settings
          Sort Key: debtors.name
          Sort Method: external merge  Disk: 82752kB
          -&gt;  Hash Join  (cost=896.60..114664.90 rows=1140 width=57) (actual time=14.889..917.360 rows=10550 loops=1)
                Output: debtors.id, debtors.name, debtor_registry.settings
                Hash Cond: (((debtor_registry.id)::character varying)::text = (debtors.registry_uuid)::text)
                -&gt;  Foreign Scan on public.debtor_registry  (cost=100.00..113832.74 rows=1137 width=48) (actual time=8.845..902.466 rows=10529 loops=1)
                      Output: debtor_registry.id, debtor_registry.company_id, debtor_registry.settings, debtor_registry.product
                      Remote SQL: SELECT id, settings FROM public.company_debtor
                -&gt;  Hash  (cost=664.60..664.60 rows=10560 width=62) (actual time=6.027..6.028 rows=10554 loops=1)
                      Output: debtors.id, debtors.name, debtors.registry_uuid
                      Buckets: 16384  Batches: 1  Memory Usage: 1108kB
                      -&gt;  Seq Scan on public.debtors  (cost=0.00..664.60 rows=10560 width=62) (actual time=0.019..4.726 rows=10560 loops=1)
                            Output: debtors.id, debtors.name, debtors.registry_uuid
  Planning Time: 0.098 ms
  JIT:
    Functions: 10
    Options: Inlining false, Optimization false, Expressions true, Deforming true
    Timing: Generation 1.609 ms, Inlining 0.000 ms, Optimization 0.674 ms, Emission 7.991 ms, Total 10.274 ms
  Execution Time: 14007.113 ms
  </code></pre>
  <p>How can I make the 1st query faster without omitting the <code>settings</code> field and the <code>ORDER BY name</code> clause and <code>LIMIT 1000</code>?</p>
  <blockquote>
  <p>UPDATE</p>
  </blockquote>
  <ol>
  <li><p>I also found this similar <a href="https://dba.stackexchange.com/q/179744/135744">question</a> but the answer does not solve my problem. Since our sorting is dynamic and we build queries based on the frontend client request.</p>
  </li>
  <li><p>Setting <code>use_remote_estimate</code> to 'true' doesn't help either. :(</p>
  </li>
  </ol>