Re: postgres_fdw pushdown problem. - Mailing list pgsql-general

"Rhys A.D. Stewart" <rhys.stewart@gmail.com> writes:
> I'm using postgres_fdw and am having a pushdown issue. The TL;DR is
> that the where clause doesn't get pushed down for the last of the
> following three queries.

> SELECT * FROM service.mrrdr_synth WHERE premises = '1057430';
> SELECT * FROM service.mrrdr_synth WHERE reading_val = '8';
> SELECT * FROM service.mrrdr_synth WHERE location_capture_time =
> '2018-10-20 12:02:53.097247-05'::timestamptz;

Well, you've got

> CREATE FOREIGN TABLE service.mrrdr_synth( ...
>     location_capture_time timestamp without time zone NULL,

so that WHERE clause is a comparison of timestamp vs timestamptz,
which depends on the active timezone setting, so it's not immutable
and won't get pushed.  (In English: the planner is afraid the remote
end might be using a different timezone setting.)  I'm pretty sure
either timestamp = timestamp or timestamptz = timestamptz would be
pushable.

            regards, tom lane