in postgres 9.5.0 i have partitioned table, that collect data by months, i tried to use new postgres feature foreign table inheritance & pushed one month of data to another postgres server, so i got foreign table. when i am running my query from my primary server, query takes 7x more time to execute query, then on another server where i have foreign table. i am not passing a lot of data by network, my query looking like
explain analyze SELECT source, global_action, paid, organic, device, count(*) as count, sum(price) as sum FROM "toys" WHERE "toys"."container_id" = 857 AND (toys.created_at >= '2015-12-02 05:00:00.000000') AND (toys.created_at <= '2015-12-30 04:59:59.999999') AND ("toys"."source" IS NOT NULL) GROUP BY "toys"."source", "toys"."global_action", "toys"."paid", "toys"."organic", "toys"."device";
HashAggregate (cost=1143634.94..1143649.10 rows=1133 width=15) (actual time=1556.894..1557.017 rows=372 loops=1) Group Key: toys.source, toys.global_action, toys.paid, toys.organic, toys.device -> Append (cost=0.00..1143585.38 rows=2832 width=15) (actual time=113.420..1507.373 rows=76593 loops=1) -> Seq Scan on toys (cost=0.00..0.00 rows=1 width=242) (actual time=0.001..0.001 rows=0 loops=1) Filter: ((source IS NOT NULL) AND (created_at >= '2015-12-02 05:00:00'::timestamp without time zone) AND (created_at <= '2015-12-30 04:59:59.999999'::timestamp without time zone) AND (container_id = 857)) -> Foreign Scan on job_stats_201512_new (cost=100.00..1143585.38 rows=2831 width=15) (actual time=113.419..1488.445 rows=76593 loops=1) Planning time: 2.990 ms Execution time: 1560.131 ms
does postgres use indexes on foreign tables ?(i have indexes defined in foreign table), if i run query directly on that server it takes - 200ms.