From: Dzmitry Nikitsin <dzmitry.nikitsin@gmail.com>
To: pgsql-performance@postgresql.org
Subject: performance issue with inherited foreign table
Date: Wed, 27 Jan 2016 18:46:00 -0500
Message-ID: <BFB9AF7E-ED23-4B29-A093-26C7A118EADE@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dzmitry.nikitsin@gmail.com
Subject: Re: performance issue with inherited foreign table
In-Reply-To: <BFB9AF7E-ED23-4B29-A093-26C7A118EADE@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox