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

>>>>> "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)