3

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?

Here are the two queries I'm comparing:

SELECT *
FROM pjm.prices
WHERE pricedate = CURRENT_DATE - 1
ORDER BY pricedate, hour, node_id;

SELECT *
FROM pjm.prices
WHERE pricedate = '2020-01-24'
ORDER BY pricedate, hour, node_id;

Here is the EXPLAIN ANALYZE from each of the queries respectively:

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  
  ->  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  

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
  ->  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

I am running PostgreSQL 12.1 on an Ubuntu 18.04 server.

Let me know if you have any further questions!

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.

geckels1
  • 347
  • 1
  • 3
  • 13
  • 3
    Does [this post](https://stackoverflow.com/questions/50164775/postgres-fdw-not-pushing-down-where-criteria) answer your question? There is also quite a bit of info in [the docs](https://www.postgresql.org/docs/12/postgres-fdw.html) (search for "IMMUTABLE"). – Brits Jan 26 '20 at 01:17
  • Yes it does! I tried putting CURRENT_DATE in a subquery, and it worked. Was no aware of IMMUTABLE, and I think it explains a lot of problems I've been having with the fdw. Thanks so much! – geckels1 Jan 26 '20 at 01:33
  • 1
    "However, it is difficult to know if this is the case as the EXPLAIN ANALYZE just lists both queries as "Foreign Scans"", right EXPLAIN output doesn't "recurse" into the foreign server. But you can set up auto_explain on the foreign server and then go inspect the log files. – jjanes Jan 26 '20 at 14:47

1 Answers1

0

I had similar issue and looked for all the possible options, just try the Vacuum full analyze on all the databases involved in FDW queries and then FDW related query.

Virender Jain
  • 91
  • 1
  • 1