Passing of where clause to remote table in FDW

Hello,

While using fdw I am trying to get and filtered data from a foreign table. Filtering using a regex in where clause as follows:

EXPLAIN ANALYZE VERBOSE SELECT CAF1.uprn
FROM XYZ as CAF1
WHERE
(regexp_replace(upper(concat(CAF1.SUB_BUILDING_NAME,CAF1.BUILDING_NAME,CAF1.BUILDING_NUMBER,CAF1.STREET_DESCRIPTION , CAF1.TOWN_NAME)), '[^a-zA-Z0-9]','') = '84WELLINGTONROADEXETER');

Following is the query plan for the query, please observe the remote SQL statement:

"Foreign Scan on abc.xyz caf1 (cost=100.00..212.40 rows=13 width=8) (actual time=0.607..590.992 rows=2 loops=1)"
" Output: uprn"
" Filter: (regexp_replace(upper(concat(caf1.sub_building_name, caf1.building_name, caf1.building_number, caf1.street_description, caf1.town_name)), '[^a-zA-Z0-9]'::text, ''::text) = '84WELLINGTONROADEXETER'::text)"
" Rows Removed by Filter: 115952"
" Remote SQL: SELECT uprn, building_name, sub_building_name, building_number, street_description, town_name FROM lmn.xyz"
"Total runtime: 591.365 ms"

What I interpret through this is that the regex is not going to the remote side, instead whole remote table is being copied onto the foreign table and then the regex is applied.

A remote SQL that I wish to have is as follows:

Remote SQL: SELECT uprn, building_name, sub_building_name, building_number, street_description, town_name FROM lmn.xyz where (regexp_replace(upper(CAF1.SUB_BUILDING_NAME||CAF1.BUILDING_NAME||CAF1.BUILDING_NUMBER||CAF1.STREET_DESCRIPTION || CAF1.TOWN_NAME), '[^a-zA-Z0-9]','')) = '84WELLINGTONROADEXETER'"

As the functions regexp_replace and upper are inbuild ones there should not be any problem passing them onto remote side as mentioned as follows:

"his 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."

Ref: https://www.postgresql.org/docs/9.3/static/postgres-fdw.html
PostgreSQL: Documentation: 9.3: postgres_fdw<https://www.postgresql.org/docs/9.3/static/postgres-fdw.html>
www.postgresql.org<http://www.postgresql.org>
F.31. postgres_fdw. The postgres_fdw module provides the foreign-data wrapper postgres_fdw, which can be used to access data stored in external PostgreSQL servers.

Do we have any option to carry the execution as I want?

Thanks and Regards,

Piyush Jaisingkar

______________________________________________________________________
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.