Обсуждение: postgres_fdw pushdown problem.

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

"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

The devil really is in the details.

> end might be using a different timezone setting.)  I'm pretty sure
> either timestamp = timestamp or timestamptz = timestamptz would be
> pushable.

Yeah, casting to plain old timestamp worked.

Thanks Tom.

Rhys
Peace & Love|Live Long & Prosper