Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0DDDD9FBF98 for ; Fri, 18 May 2007 13:31:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 43512-07 for ; Fri, 18 May 2007 13:31:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from joeconway.com (joeconway.com [68.15.9.201]) by postgresql.org (Postfix) with ESMTP id 4FA179FBFC9 for ; Fri, 18 May 2007 13:31:13 -0300 (ADT) Received: from [206.190.70.130] (account jconway HELO [172.16.1.115]) by joeconway.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 5004414; Fri, 18 May 2007 09:31:12 -0700 Message-ID: <464DD4CF.5090807@joeconway.com> Date: Fri, 18 May 2007 09:31:11 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.8.0.10) Gecko/20070223 Fedora/1.0.8-0.5.1.fc5 SeaMonkey/1.0.8 MIME-Version: 1.0 To: Jessica Richard CC: pgsql-admin@postgresql.org Subject: Re: select from dblink very slow References: <267133.52846.qm@web56415.mail.re3.yahoo.com> In-Reply-To: <267133.52846.qm@web56415.mail.re3.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/196 X-Sequence-Number: 25516 Jessica Richard wrote: > I need to joint select from table A in the current database A and dblink > to table B in database B, the result coming from tableB in db B is very > slow because I have to select all the rows from tableB from dblink then > do "where" with table A in the current db....doing dblink this way is > very very slow.... > > Any idea on how to improve this? About the only thing I can think of using dblink would be to use a PL/pgSQL function to dynamically build and execute your query in such a way that the WHERE clause is pushed to the remote query. Someone else suggested dbi-link as an alternative -- I don't know for sure, but suspect it will suffer from the same issue as dblink. I also saw a suggestion of using different schemas instead of different databases. This is the best solution if it is feasible. Finally, I don't know much about it, but perhaps PL/proxy would make it easier to push down your WHERE clause: https://developer.skype.com/SkypeGarage/DbProjects/PlProxy HTH, Joe