Passing of where clause to remote table in FDW

["Jaisingkar, Piyush" <Piyush.Jaisingkar@xxxxxxxxxxx>]

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

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.