Re: BUG #15287: postgres_fdw: the "WHERE date_trunc('day', dt) = 'YYYY-MM-DD' does not push to remote.

>>>>> "Andres" == Andres Freund <andres(at)anarazel(dot)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)