Skip site navigation (1) Skip section navigation (2)

Re: select from dblink very slow

From: Joe Conway <mail(at)joeconway(dot)com>
To: Jessica Richard <rjessil(at)yahoo(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: select from dblink very slow
Date: 2007-05-18 16:31:11
Message-ID: 464DD4CF.5090807@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

pgsql-admin by date

Next:From: Tom LaneDate: 2007-05-18 16:56:52
Subject: Re: Frequent pg_clog on system
Previous:From: Abu MushayeedDate: 2007-05-18 16:00:06
Subject: CPU Intensive query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group