postgresql slow query (dblink and inner join)

Patrick Pelino picture Patrick Pelino · Dec 21, 2012 · Viewed 10.9k times · Source

I have this query containing a dblink since I need to connect to another database and it seems so slow (50.343 seconds for just 124 records). Is there any way to make it fast? Below is the code:

select * 
from 
    customer  
    INNER JOIN  
    dblink('host=192.168.3.9 dbname=db2 user=postgres password=postgres', '
        SELECT
            status,
            last_churn_1,
            attempts,
            last_dialed,
            lead_id,
            date_added
        FROM campaign_customer
        ') AS table2 (
            status char(50),
            last_churn_1 char(50),
            attempts int,
            last_dialed char(250),
            lead_id char(8),
            date_added char(50)
        ) ON customer.phone1 = table2.last_dialed
where customer.leadid = '3434' and table2.lead_id='3434'

Answer

Clodoaldo Neto picture Clodoaldo Neto · Dec 21, 2012

As Daniel suggested:

select * 
from 
    customer  
    INNER JOIN  
    dblink('host=192.168.3.9 dbname=db2 user=postgres password=postgres', $$
        SELECT
            status,
            last_churn_1,
            attempts,
            last_dialed,
            lead_id,
            date_added
        FROM campaign_customer
        where lead_id='3434'
        $$) AS table2 (
            status char(50),
            last_churn_1 char(50),
            attempts int,
            last_dialed char(250),
            lead_id char(8),
            date_added char(50)
        ) ON customer.phone1 = table2.last_dialed
where customer.leadid = '3434'