User-Agent: Microsoft-MacOutlook/0.0.0.151217
Date: Wed, 27 Jan 2016 18:46:00 -0500
Subject: performance issue with inherited foreign table 
From: Dzmitry Nikitsin <dzmitry.nikitsin@gmail.com>
To: <pgsql-performance@postgresql.org>
Message-ID: <BFB9AF7E-ED23-4B29-A093-26C7A118EADE@gmail.com>
Thread-Topic: performance issue with inherited foreign table 
Mime-version: 1.0
Content-type: multipart/alternative;
	boundary="B_3536765162_1581126399"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

> This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

--B_3536765162_1581126399
Content-type: text/plain;
	charset="UTF-8"
Content-transfer-encoding: 7bit

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.


--B_3536765162_1581126399
Content-type: text/html;
	charset="UTF-8"
Content-transfer-encoding: quoted-printable

<html><head></head><body style=3D"word-wrap: break-word; -webkit-nbsp-mode: s=
pace; -webkit-line-break: after-white-space; color: rgb(0, 0, 0); font-size:=
 14px; font-family: Calibri, sans-serif;"><div><div><div><div class=3D"post-te=
xt" itemprop=3D"text" style=3D"margin: 0px 0px 5px; padding: 0px; border: 0px; f=
ont-size: 15px; width: 660px; word-wrap: break-word; line-height: 1.3; color=
: rgb(34, 36, 38); font-family: 'Helvetica Neue', Helvetica, Arial, sans-ser=
if; widows: 1; background-color: rgb(255, 255, 255);"><p style=3D"margin: 0px =
0px 1em; padding: 0px; border: 0px; clear: both;">in postgres 9.5.0 i have p=
artitioned table, that collect data by months, i tried to use new postgres f=
eature foreign table inheritance &amp; pushed one month of data to another p=
ostgres server, so i got foreign table. when i am running my query from my p=
rimary server, query takes 7x more time to execute query, then on another se=
rver where i have foreign table. i am not passing a lot of data by network, =
my query looking like</p><p style=3D"margin: 0px 0px 1em; padding: 0px; border=
: 0px; clear: both;">explain analyze SELECT source, global_action, paid, org=
anic, device, count(*) as count, sum(price) as sum FROM "toys" WHERE "toys".=
"container_id" =3D 857 AND (toys.created_at &gt;=3D '2015-12-02 05:00:00.000000'=
) AND (toys.created_at &lt;=3D '2015-12-30 04:59:59.999999') AND ("toys"."sour=
ce" IS NOT NULL) GROUP BY "toys"."source", "toys"."global_action", "toys"."p=
aid", "toys"."organic", "toys"."device";</p><p style=3D"margin: 0px 0px 1em; p=
adding: 0px; border: 0px; clear: both;">HashAggregate (cost=3D1143634.94..1143=
649.10 rows=3D1133 width=3D15) (actual time=3D1556.894..1557.017 rows=3D372 loops=3D1)=
 Group Key: toys.source, toys.global_action, toys.paid, toys.organic, toys.d=
evice -&gt; Append (cost=3D0.00..1143585.38 rows=3D2832 width=3D15) (actual time=3D1=
13.420..1507.373 rows=3D76593 loops=3D1) -&gt; Seq Scan on toys (cost=3D0.00..0.00=
 rows=3D1 width=3D242) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((sourc=
e IS NOT NULL) AND (created_at &gt;=3D '2015-12-02 05:00:00'::timestamp withou=
t time zone) AND (created_at &lt;=3D '2015-12-30 04:59:59.999999'::timestamp w=
ithout time zone) AND (container_id =3D 857)) -&gt; Foreign Scan on job_stats_=
201512_new (cost=3D100.00..1143585.38 rows=3D2831 width=3D15) (actual time=3D113.419=
..1488.445 rows=3D76593 loops=3D1) Planning time: 2.990 ms Execution time: 1560.=
131 ms</p><p style=3D"margin: 0px 0px 1em; padding: 0px; border: 0px; clear: b=
oth;">does postgres use indexes on foreign tables ?(i have indexes defined i=
n foreign table), if i run query directly on that server it takes - 200ms.</=
p><div><br></div></div></div></div></div></body></html>

--B_3536765162_1581126399--