>>>>> "Andres" == Andres Freund <andres@anarazel.de> writes:
>> the clause:
>> WHERE date_trunc('day'::text, dt::timestamp without time zone) =
>> 'yyyy-mm-dd'
>> does not get shipped to the foreign server.
Andres> You're probably going to have a higher likelihood of getting
Andres> the bug fixed quickly if you'd include a full reproducer.
On remote:
create table foreigntab (dt timestamp);
insert into foreigntab
select timestamp '2000-01-01' + (random()*568036800)
* interval '1 second'
from generate_series(1,100000);
create index on foreigntab (date_trunc('day',dt));
analyze foreigntab;
On local:
-- create foreigntab as a foreign table or import the foreign schema
-- e.g.
create server foreigndb foreign data wrapper postgres_fdw
options (dbname 'foreigndb');
create user mapping for postgres server foreigndb;
import foreign schema public from server foreigndb into public;
postgres=# explain analyze select * from foreigntab where date_trunc('day', dt) = '2018-07-20';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Foreign Scan on foreigntab (cost=100.00..199.60 rows=13 width=8) (actual time=637.399..637.399 rows=0 loops=1)
Filter: (date_trunc('day'::text, dt) = '2018-07-20 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 100000
--
Andrew (irc:RhodiumToad)