title: PostgreSQL postgres_fdw querying very slowly on large foreign table when using CURRENT_DATE - 1 but not with hardcoded date
body: <p>I am using postgres_fdw to connect to another PostgreSQL DB. Sometimes the performance of a query works fine, but other times a query can take an abnormally long time. I have run into an issue where if I use the function CURRENT_DATE, the query takes ~30 minutes but if I hard code the same date, the query takes 1 sec. Looking at the EXPLAIN ANALYZE of both queries, it appears when I use CURRENT_DATE, it scans the entire foreign table, but when I hard code the date, it only scans the rows it needs. It's as if using CURRENT_DATE does not use the table index, but hard coding the date does. However, it is difficult to know if this is the case as the EXPLAIN ANALYZE just lists both queries as "Foreign Scans". Why would these two queries have such different execution times?</p>
  
  <p>Here are the two queries I'm comparing:</p>
  
  <pre><code>SELECT *
  FROM pjm.prices
  WHERE pricedate = CURRENT_DATE - 1
  ORDER BY pricedate, hour, node_id;
  </code></pre>
  
  <pre><code>SELECT *
  FROM pjm.prices
  WHERE pricedate = '2020-01-24'
  ORDER BY pricedate, hour, node_id;
  </code></pre>
  
  <p>Here is the EXPLAIN ANALYZE from each of the queries respectively:</p>
  
  <pre><code>Sort  (cost=4620220.79..4621390.74 rows=467980 width=120) (actual time=2258506.411..2258509.528 rows=31752 loops=1)  
    Sort Key: hour, node_id  
    Sort Method: quicksort  Memory: 5196kB  
    -&gt;  Foreign Scan on prices  (cost=100.00..4576146.22 rows=467980 width=59) (actual time=8737.505..2258486.086 rows=31752 loops=1)  
          Filter: (pricedate = (CURRENT_DATE - 1))  
          Rows Removed by Filter: 93563101  
  Planning Time: 107.264 ms  
  Execution Time: 2258547.132 ms  
  </code></pre>
  
  <pre><code>Sort  (cost=122729.63..122816.37 rows=34694 width=120) (actual time=1006.677..1009.733 rows=31752 loops=1)
    Sort Key: hour, node_id
    Sort Method: quicksort  Memory: 5196kB
    -&gt;  Foreign Scan on prices  (cost=753.45..120113.29 rows=34694 width=59) (actual time=987.827..989.274 rows=31752 loops=1)
  Planning Time: 108.424 ms
  Execution Time: 1046.897 ms
  </code></pre>
  
  <p>I am running PostgreSQL 12.1 on an Ubuntu 18.04 server.</p>
  
  <p>Let me know if you have any further questions!</p>
  
  <p>EDIT: I forgot to add when I run the query with CURRENT_DATE locally (as in I actually log into the DB my fdw is connecting to and run it from there), it only takes 1 sec.</p>