X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 34782475D6E for ; Mon, 3 Mar 2003 21:43:28 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 94334-10 for ; Mon, 3 Mar 2003 21:43:26 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id A18C0475C60 for ; Mon, 3 Mar 2003 21:43:26 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.8/8.12.8) with ESMTP id h242hRx6019785; Mon, 3 Mar 2003 21:43:27 -0500 (EST) To: Dawn Hollingsworth Cc: pgsql-sql@postgresql.org Subject: Re: Query Against a dblink View Takes Too Long to Return In-reply-to: <1046710746.1290.3.camel@kaos> References: <1046710746.1290.3.camel@kaos> Comments: In-reply-to Dawn Hollingsworth message dated "03 Mar 2003 16:58:58 +0000" Date: Mon, 03 Mar 2003 21:43:27 -0500 Message-ID: <19784.1046745807@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/107 X-Sequence-Number: 12147 Dawn Hollingsworth writes: > Nested Loop (cost=0.00..466.51 rows=1 width=24) (actual > time=226.39..1018072.99 rows=9353 loops=1) > -> Subquery Scan t1 (cost=0.00..0.01 rows=1 width=0) (actual > time=225.99..345.86 rows=9353 loops=1) > -> Result (cost=0.00..0.01 rows=1 width=0) (actual > time=225.98..303.29 rows=9353 loops=1) > -> 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) > Total runtime: 1018092.69 msec The planner evidently thinks that dblink() will return only one row, and consequently it chooses a plan that would be fast in that case ... but is dog-slow for 9000 rows. Probably the easiest answer is to update to 7.3 and use the new function-in-FROM syntax for invoking dblink. That should result in more useful assumptions about the number of rows returned. regards, tom lane