Postgresql – Changing Postgres 9.6 to behave more like 9.5

Postgres can use indexes on the foreign server. But there are quite a few more obstacles than for local tables. Read the chapter Remote Query Optimization in the manual.

Comments in the current source code of postgres_fdw.c are revealing, too:

521  * [...] For a foreign
522  * table, we don't know what indexes are present on the remote side but
523  * want to speculate about which ones we'd like to use if they existed.
...
675      * need to be a bit cautious here.  It would sure be nice to have a local
676      * cache of information about remote index definitions...
...
722      * corresponds to SeqScan path of regular tables (though depending on what
723      * baserestrict conditions we were able to send to remote, there might
724      * actually be an indexscan happening there).  

Index

This index of yours looks good for it:

"toys_201512_new_container_id_created_at" btree (container_id, created_at)

If you have many NULL values you might even make that a partial index by appending WHERE source IS NOT NULL, making the index look even better for the Postgres query planner.

Statistics for query planning

Make sure the query planner can work with valid statistics. The numbers in your EXPLAIN output show quite a mismatch:

Foreign Scan on toys_201512_new  (cost=100.00..1143585.38 rows=2831 width=15)
                           (actual time=113.419..1488.445 rows=76593 loops=1)

27x as many rows as Postgres expected were actually returned . The manual:

Running ANALYZE on the foreign table is the way to update the local statistics; this will perform a scan of the remote table and then calculate and store statistics just as though the table were local. Keeping local statistics can be a useful way to reduce per-query planning overhead for a remote table — but if the remote table is frequently updated, the local statistics will soon be obsolete.

Since accessing foreign tables is potentially expensive / delicate, this does not happen automatically. Foreign tables are not covered by autovacuum. The manual:

Foreign tables are analyzed only when explicitly selected.

If the remote table changes a lot, you might want to activate use_remote_estimate. The manual:

This option, which can be specified for a foreign table or a foreign server, controls whether postgres_fdw issues remote EXPLAIN commands to obtain cost estimates. A setting for a foreign table overrides any setting for its server, but only for that table. The default is false.

Finally, test to see what is actually sent to the foreign server:

The query that is actually sent to the remote server for execution can be examined using EXPLAIN VERBOSE.

Query

Your query decluttered and formatted, with one minor improvement:

SELECT source, global_action, paid, organic, device
     , count(*) AS count, sum(price) AS sum
FROM   toys
WHERE  container_id = 857
AND    created_at >= '2015-12-02 05:00:00'
AND    created_at <  '2015-12-30 05:00:00'
AND    created_at <= '2015-12-30 04:59:59.999999'
AND    source IS NOT NULL
GROUP  BY source, global_action, paid, organic, device;

Simpler, cleaner and also matches your CHECK constraint better and avoids possible corner case problems.

Try this query:

SELECT name, context
FROM pg_settings
WHERE name ilike '%log%';

The pg_settings view gives the context for any setting. sighup is a reload and postmaster is a restart.