postgres_fdw pushdown problem.

["Rhys A.D. Stewart" <rhys.stewart@xxxxxxxxx>]

Greetings all,

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;

The documentation that I have read doesn't indicate why this would be
the case. So looking to figure out why this is happening.

The following obtains:

Local server version: PostgreSQL 10.4, compiled by Visual C++ build 1800, 64-bit

foreign table DDL:

CREATE FOREIGN TABLE service.mrrdr_synth(
    premises text NULL COLLATE pg_catalog."default",
    meter_num text NULL COLLATE pg_catalog."default",
    routeid text NULL COLLATE pg_catalog."default",
    lineinfo text NULL COLLATE pg_catalog."default",
    reading_val text NULL COLLATE pg_catalog."default",
    location_capture_time timestamp without time zone NULL,
    g geometry NULL
)
    SERVER "ldceng006-pc"
    OPTIONS (schema_name 'mrrdr', table_name 'synth');

Foreign server version: PostgreSQL 10.1, compiled by Visual C++ build
1800, 64-bit.

CREATE MATERIALIZED VIEW mrrdr.synth
AS
 SELECT cr.premises,
    mr.meter_num,
    cr.routeid,
    cr.lineinfo,
    responses.reading_val,
    responses.location_capture_time,
    responses.g
   FROM mrrdr.customer_record cr
     JOIN mrrdr.meter_record mr USING (premises, routeid)
     JOIN mrrdr.responses USING (meter_num, routeid)
  WHERE cr.routeid::text >= '20160701001021'::text
WITH DATA;

CREATE INDEX synth_g_idx
    ON mrrdr.synth USING gist
    (g);
CREATE INDEX synth_premises_idx
    ON mrrdr.synth USING btree
    (premises COLLATE pg_catalog."default");
CREATE INDEX synth_routeid_idx
    ON mrrdr.synth USING btree
    (routeid COLLATE pg_catalog."default");
CREATE INDEX synth_substring_idx
    ON mrrdr.synth USING btree
    ("substring"(routeid::text, 10, 5) COLLATE pg_catalog."default");

The relations in the above query are all materialized views and all
those materialized views pull from foreign tables using ogr_fdw, not
sure if this is pertinent info.

Regards,

Rhys
Peace & Love|Live Long & Prosper