On Wed, Dec 7, 2011 at 3:37 PM, Marc Mamin <M.Mamin@xxxxxxxxxxxx> wrote: >> > >> > I find dblink being a nice tool as long as the data volume to >> transfer >> > remains low. >> > I've evaluated it to implement a clustered Postgres environment, but >> > gave it up due to the poor performances. >> > Still waiting for the binary transfer before the next try ;-) >> >> Binary transfer is not a super big deal in terms of performance >> actually in the general case. It's only substantially faster in a few >> cases like timestamp, geo types, and of course bytea. Lack of >> parameterization I find to be a bigger deal actually -- it's more of a >> usability headache than a performance thing. >> >> Also FYI binary dblink between databases is going to be problematic >> for any non built in type unless the type oids are synchronized across >> databases. >> >> merlin > > Thanks, > ... so I don't really understand where all the time get lost in the > example I posted a few weeks ago: > > http://archives.postgresql.org/pgsql-general/2011-09/msg00436.php you wrote: "select count(*) from ( select * from dblink('a','select * from test_cluster')as t1(a int) union all select * from dblink('a','select * from test_cluster')as t1(a int) union all select * from dblink('a','select * from test_cluster')as t1(a int) )foo is about 5 times slower than an equivalent query run locally. working with asynchron. queries (dblink_send_query) does not bring much benefit so that much time seems to be spent for transfer and merge" it's not exactly fair to compare dblink to local query -- dblink method requires having to marshal all the data over the protoocl and un-marshal on the other end. I was seeing 3-5x times difference vs local query but this is to be expected. note the 'union all' had nothing to so with your performance problems. Also the querying server can do a very special trick for count(*) since it only needs to check tuple visibility that can't be done when doing select count(*) from (<dblink_queries>). My point up thread is that dblink is going to be comparable to other methods that involve querying the data off the server and doing the processing on the client side. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
