Re: Joins between foreign tables

On Mon, Jun 8, 2015 at 10:29 AM, Jason Dusek <jason(dot)dusek(at)gmail(dot)com> wrote:
> The databases involved are all Postgres 9.4 or 9.3. The FDW is the Postgres
> FDW.
>
> The join node (the one from which queries are issued) is Postgres 9.4
> installed yesterday from the Postgres Apt repository. It's using the version
> of the wrapper that is shipped with that package.
>
> The databases being queried -- the ones with the actual tables -- are both
> Postgres 9.3.5, on Amazon RDS.
>

Sorry, I forgot to include pgsql-general on the address list for my reply.

It seems that postgres_fdw in Postgres 9.3 and 9.4 supports where
clause push-down according to the documentation:

http://www.postgresql.org/docs/9.3/static/postgres-fdw.html

"postgres_fdw attempts to optimize remote queries to reduce the amount
of data transferred from foreign servers. This is done by sending
query WHERE clauses to the remote server for execution, and by not
retrieving table columns that are not needed for the current query. To
reduce the risk of misexecution of queries, WHERE clauses are not sent
to the remote server unless they use only built-in data types,
operators, and functions. Operators and functions in the clauses must
be IMMUTABLE as well."

I'm guessing that postgres_fdw sees that one of your function calls is
IMMUTABLE, so it thinks it is unsafe to push-down. It is probably your
call to NOW(). You might want to try replacing that with a literal
somehow, if you can.

Geoff