postgres_fdw foreign table performance Issue

From: Shuwn Yuan Tee <shuwnyuan(at)binary(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: postgres_fdw foreign table performance Issue
Date: 2014-01-06 08:19:23
Message-ID: CABoYu+=Fz1YK8ikvXXhbyWdmC5-eKHWBFZ-JSDhgojh9A08TxQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We have client databases in 3 different region in the world, namely:
cr_master, mx_master & mlt_master db. These 3 databases have exactly the
same structure, with different subset of client data.

For reporting purpose, we aggregate these 3 db to single collector, using
Bucardo replication. Recently, we start experimenting with postgres_fdw, to
check for better alternative to Bucardo aggregation. The purpose is to
setup a usable fdw-collector, to be used in reporting which aggregates all
client data from cr_master, mx_master & mlt_master.

Here's our setup:
1) Host machine - running CentOS

2) 4 Openvz instances - running postgres 9.3:
a) cr_replica: binary replica for cr_master
b) mx_replica: binary replica for mx_master
c) mlt_replica: binary replica for mlt_master
d) fdw-collector

With fdw-collector, remote servers are pointing to those binary replicas,
to access the remote tables. However, I run into this performace issue:

1) Query cr_replica directly:
select * from transaction.transaction where transaction_time >=
'2013-12-01' and transaction_time < '2014-01-01'

Total runtime => 10s

2) Query fdw-collector, with remote table to cr_replica:
select * from transaction.transaction_cr where transaction_time >=
'2013-12-01' and transaction_time < '2014-01-01';

Total runtime => 60s

The performance puzzles me. Any idea what makes accessing remote table 6
times slower? In this setup, "remote server" are actually local postgres
instances running on same host as fdw-collector. There is no network
latency involved. Is this a known performance issue? Or I did some wrong
configuration somewhere?

Any help is much appreciated, thanks.

regards,
shuwn yuan

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2014-01-06 08:25:02 Re: Dynamic SQL - transition from ms to pg
Previous Message Michael Paquier 2014-01-06 06:18:42 Re: Dynamic SQL - transition from ms to pg