Hello, When query is executed in function that has language set to "sql" then parameters passed to it are not considered as either externally supplied parameters or constants as far as partitioning goes. It will scan all partitions, despite parameters clearly limiting it to just one.
Changing function to plpgsql or doing plain SQL query does not share this issue. Any idea why that is and if this is intended? SELECT col1, col2 FROM table1 WHERE date_col = '2017-07-30' AND some_col = ANY(array[1,2]) This is query plan that I would expect to get: Append (cost=0.00..26.05 rows=2 width=36) -> Seq Scan on table1 (cost=0.00..0.00 rows=1 width=36) Filter: ((some_col = ANY ('{1,2}'::integer[])) AND (date_col = '2017-07-30'::date)) -> Seq Scan on "part$_table1_201707" (cost=0.00..26.05 rows=1 width=36) Filter: ((some_col = ANY ('{1,2}'::integer[])) AND (date_col = '2017-07-30'::date)) What I am likely getting is this tho: Append (cost=0.01..156.55 rows=3 width=36) InitPlan 1 (returns $0) -> Result (cost=0.00..0.01 rows=1 width=4) -> Seq Scan on table1 (cost=0.00..0.00 rows=1 width=36) Filter: ((some_col = ANY ('{1,2}'::integer[])) AND (date_col = $0)) -> Seq Scan on "part$_table1_201707" (cost=0.00..26.05 rows=1 width=36) Filter: ((some_col = ANY ('{1,2}'::integer[])) AND (date_col = $0)) -> Foreign Scan on "part$_table1_201603" (cost=100.00..130.49 rows=1 width=36) Something like this happens if I do this plain SQL query: SELECT col1, col2 FROM table1 WHERE date_col = (SELECT '2017-07-30'::date) --problem here AND some_col = ANY(array[1,2]) Below is full code that can be used to reproduce this issue. CREATE SERVER broken_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'broken_server', dbname 'postgres', port '5432'); CREATE USER MAPPING FOR postgres SERVER broken_server OPTIONS (user 'foreign_username', password 'foreign_password'); CREATE TABLE table1 (id serial PRIMARY KEY, date_col date, some_col int, col1 int, col2 text); CREATE TABLE part$_table1_201707 () INHERITS (table1); ALTER TABLE part$_table1_201707 ADD CONSTRAINT part$_table1_201707_date_chk CHECK (date_col BETWEEN '2017-07-01'::date AND '2017-07-31'::date); CREATE FOREIGN TABLE part$_table1_201603 () INHERITS (table1) SERVER broken_server OPTIONS (schema_name 'public', table_name 'part$_table1_201603'); ALTER TABLE part$_table1_201603 ADD CONSTRAINT part$_table1_201603_date_chk CHECK (date_col BETWEEN '2016-03-01'::date AND '2016-03-31'::date); CREATE OR REPLACE FUNCTION function_plpgsql(param1 date, param2 int[]) RETURNS TABLE(col1 int, col2 text) LANGUAGE plpgsql SECURITY DEFINER AS $function$ BEGIN -- RETURN QUERY SELECT t.col1, t.col2 FROM table1 AS t WHERE date_col = param1 AND some_col = ANY(param2); -- END; $function$; CREATE OR REPLACE FUNCTION function_sql(param1 date, param2 int[]) RETURNS TABLE(col1 int, col2 text) LANGUAGE SQL SECURITY DEFINER AS $function$ -- SELECT t.col1, t.col2 FROM table1 AS t WHERE date_col = param1 AND some_col = ANY(param2) -- $function$; CREATE OR REPLACE FUNCTION function_sql_hardcoded(param1 date, param2 int[]) RETURNS TABLE(col1 int, col2 text) LANGUAGE SQL SECURITY DEFINER AS $function$ -- SELECT t.col1, t.col2 FROM table1 AS t WHERE date_col = '2017-07-30'::date AND some_col = ANY(param2) -- $function$; EXPLAIN ANALYZE SELECT * FROM function_sql('2017-07-30'::date, array[1,2]); -- ERROR: could not connect to server "broken_server" EXPLAIN ANALYZE SELECT * FROM function_plpgsql('2017-07-30'::date, array[1,2]); --Executes sucessfully, as expected EXPLAIN ANALYZE SELECT * FROM function_sql_hardcoded('2017-07-30'::date, array[1,2]); --Executes sucessfully, but useless