Thread: 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
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.
"Jaisingkar, Piyush" <Piyush.Jaisingkar@nttdata.com> writes: > 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'); concat() is not immutable (it's only stable) so it can't be sent for remote execution. Use the || operator instead. regards, tom lane
Thanks for the suggestion, Used || , actually I was reluctant to use this because my columns could have contained null values.But that I have handled using COALESCE. -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Thursday, October 13, 2016 7:37 PM To: Jaisingkar, Piyush Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Passing of where clause to remote table in FDW "Jaisingkar, Piyush" <Piyush.Jaisingkar@nttdata.com> writes: > 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'); concat() is not immutable (it's only stable) so it can't be sent for remote execution. Use the || operator instead. regards, tom lane ______________________________________________________________________ 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.