From rjessil@yahoo.com Fri May 18 13:20:12 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0E7249FC2F4 for ; Fri, 18 May 2007 10:20:12 -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 73487-06 for ; Fri, 18 May 2007 10:20:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web56415.mail.re3.yahoo.com (web56415.mail.re3.yahoo.com [216.252.111.94]) by postgresql.org (Postfix) with SMTP id ADC839FC322 for ; Fri, 18 May 2007 10:20:06 -0300 (ADT) Received: (qmail 54085 invoked by uid 60001); 18 May 2007 13:20:05 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=IttqLq77RIfWf4J8JmuMSU47k75htmgsQhM/gXz6FNXXBYA9ziCMNniWxzwQZL+pqQCTNWJLyJz+VM0BKfPPklGg8nxC1T2zXaJtzpp3mq6qV3Pp8InzVkeN7hGASyVqZhy2kLAaJNvvB8ZfULmp31+jn/mZ9+Nv9PGnelLaObA=; X-YMail-OSG: w4wJY_wVM1m.a3wZ6tR42yJo_w4V8Sp6sDowmrvjn4QX86dWRhB13TKLcvah3Lftpg-- Received: from [198.119.134.32] by web56415.mail.re3.yahoo.com via HTTP; Fri, 18 May 2007 06:20:05 PDT Date: Fri, 18 May 2007 06:20:05 -0700 (PDT) From: Jessica Richard Subject: select from dblink very slow To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-827653476-1179494405=:52846" Content-Transfer-Encoding: 8bit Message-ID: <267133.52846.qm@web56415.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.437 tagged_above=0 required=5 tests=AWL=-1.179, BAYES_20=-0.74, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, DNS_FROM_RFC_WHOIS=1.447, HTML_MESSAGE=0.001 X-Spam-Level: * X-Archive-Number: 200705/183 X-Sequence-Number: 25503 --0-827653476-1179494405=:52846 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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? Thanks, Jessica --------------------------------- Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more. --0-827653476-1179494405=:52846 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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?

Thanks,
Jessica


Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more. --0-827653476-1179494405=:52846-- From mail@joeconway.com Fri May 18 16:31:20 2007 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