Query Against a dblink View Takes Too Long to Return - Mailing list pgsql-sql

<br /><br /> PostgreSQL Version: 7.2.1 <br /> OS : Red Hat 7.3 with Kernel 2.4.18-5 and SGI_XFS <br /><br /> Currently
wehave a view defined: <br /> CREATE VIEW db1_info_view AS <br />               SELECT
CAST(dblink_tok(t1.dblink_db1_info,0)AS MACADDR)   AS id1, <br />                                 
CAST(dblink_tok(t1.dblink_db1_info,1)AS MACADDR)   AS id2, <br />                           
textToBoolean(dblink_tok(t1.dblink_db1_info,2))             AS valid, <br />                                 
CAST(dblink_tok(t1.dblink_db1_info,3)AS TIMESTAMPTZ) AS modify_time <br />               FROM (SELECT
dblink('hostaddr=127.0.0.1port=5432 dbname=db1 user=postgres', <br />                          'SELECT id1, id2, valid,
modify_timeFROM db1_info') AS dblink_db1_info) AS t1; <br /><br /> Periodically we update information in the second
databasebased upon the modify time from this view. This is the query that is run from a stored procedure. I've
separatedit out of the stored procedure because running it by hand in psql has the same problem. <br /><br /> SELECT
v.id1,v.id2, v.valid, v.modify_time <br /> FROM db1_info_view v LEFT OUTER JOIN db2_info d ON (v.id1 = d.id1 AND v.id2
=d.id2) WHERE v.modify_time >= d.modify_time; <br /><br /> Until recently this has worked perfectly.  We started
increasingthe number of records in these tables and now we're running into the problem where the SELECT statement does
notreturn. I'm not talking about a lot of records. Both tables in the query only have about 9,000 records each. <br
/><br/> I can do a SELECT COUNT(*) or SELECT * on the view and it comes back instantly. I can SELECT * and ORDER BY
modify_timeand it comes back instantly. As soon as I try to join the view to another table the query takes 10 to 20
minutesto come back. <br /><br /> NOTICE:  QUERY PLAN: <br /><br /> Nested Loop  (cost=0.00..466.51 rows=1 width=24)
(actualtime=226.39..1018072.99 rows=9353 loops=1) <br />   ->  Subquery Scan t1  (cost=0.00..0.01 rows=1 width=0)
(actualtime=225.99..345.86 rows=9353 loops=1) <br />         ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual
time=225.98..303.29rows=9353 loops=1) <br />   ->  Seq Scan on allowed_station_view av  (cost=0.00..162.53 rows=9353
width=20)(actual time=0.01..31.77 rows=9353 loops=9353) <br /> Total runtime: 1018092.69 msec <br /><br /> EXPLAIN <br
/><br/> If I create a temporary table by selecting everything from the view and then do the join it comes back
instantly.<br /><br /> NOTICE:  QUERY PLAN: <br /><br /> Merge Join  (cost=69.83..526.68 rows=1000 width=40) (actual
time=109.03..247.78rows=9353 loops=1) <br />   ->  Index Scan using allowed_station_view_pkey on
allowed_station_viewav  (cost=0.00..426.88 rows=9353 width=20) (actual time=0.03..41.93 rows=9353 loops=1) <br />  
-> Sort  (cost=69.83..69.83 rows=1000 width=20) (actual time=108.95..120.77 rows=9353 loops=1) <br />         -> 
SeqScan on allowed_station_view_test al  (cost=0.00..20.00 rows=1000 width=20) (actual time=0.07..26.67 rows=9353
loops=1)<br /> Total runtime: 261.97 msec <br /><br /> EXPLAIN <br /><br /> Am I misusing dblink? <br /><br /> Dawn
Hollingsworth<br /> AirDefense, Inc.