X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B47C447615F for ; Fri, 28 Feb 2003 13:21:33 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 86201-08 for ; Fri, 28 Feb 2003 13:21:20 -0500 (EST) Received: from smaug.securitymatrix.com (unknown [64.25.5.177]) by postgresql.org (Postfix) with ESMTP id F417F4761EB for ; Fri, 28 Feb 2003 11:58:48 -0500 (EST) Received: from kaos.securitymatrix.com ([172.16.0.101]) by smaug.securitymatrix.com (8.11.6/8.11.6) with ESMTP id h1SGvJp06650 for ; Fri, 28 Feb 2003 11:57:25 -0500 Subject: Query Against a dblink View Takes Too Long to Return From: Dawn Hollingsworth To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary="=-DiLhJBbdDIAeVGVY7FuY" X-Mailer: Ximian Evolution 1.0.8 Date: 28 Feb 2003 12:54:48 +0000 Message-Id: <1046436895.4488.214.camel@kaos> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/616 X-Sequence-Number: 12014 --=-DiLhJBbdDIAeVGVY7FuY Content-Type: text/plain Content-Transfer-Encoding: 7bit PostgreSQL Version: 7.2.1 OS : Red Hat 7.3 with Kernel 2.4.18-5 and SGI_XFS Currently we have a view defined: CREATE VIEW db1_info_view AS SELECT CAST(dblink_tok(t1.dblink_db1_info,0) AS MACADDR) AS id1, CAST(dblink_tok(t1.dblink_db1_info,1) AS MACADDR) AS id2, textToBoolean(dblink_tok(t1.dblink_db1_info,2)) AS valid, CAST(dblink_tok(t1.dblink_db1_info,3) AS TIMESTAMPTZ) AS modify_time FROM (SELECT dblink('hostaddr=127.0.0.1 port=5432 dbname=db1 user=postgres', 'SELECT id1, id2, valid, modify_time FROM db1_info') AS dblink_db1_info) AS t1; Periodically we update information in the second database based upon the modify time from this view. This is the query that is run from a stored procedure. I've separated it out of the stored procedure because running it by hand in psql has the same problem. SELECT v.id1, v.id2, v.valid, v.modify_time 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; Until recently this has worked perfectly. We started increasing the number of records in these tables and now we're running into the problem where the SELECT statement does not return. I'm not talking about a lot of records. Both tables in the query only have about 9,000 records each. I can do a SELECT COUNT(*) or SELECT * on the view and it comes back instantly. I can SELECT * and ORDER BY modify_time and it comes back instantly. As soon as I try to join the view to another table the query takes 10 to 20 minutes to come back. NOTICE: QUERY PLAN: 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 EXPLAIN If I create a temporary table by selecting everything from the view and then do the join it comes back instantly. NOTICE: QUERY PLAN: Merge Join (cost=69.83..526.68 rows=1000 width=40) (actual time=109.03..247.78 rows=9353 loops=1) -> Index Scan using allowed_station_view_pkey on allowed_station_view av (cost=0.00..426.88 rows=9353 width=20) (actual time=0.03..41.93 rows=9353 loops=1) -> Sort (cost=69.83..69.83 rows=1000 width=20) (actual time=108.95..120.77 rows=9353 loops=1) -> Seq Scan 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) Total runtime: 261.97 msec EXPLAIN Am I misusing dblink? Dawn Hollingsworth AirDefense, Inc. --=-DiLhJBbdDIAeVGVY7FuY Content-Type: text/html; charset=utf-8

PostgreSQL Version: 7.2.1
OS : Red Hat 7.3 with Kernel 2.4.18-5 and SGI_XFS

Currently we have a view defined:
CREATE VIEW db1_info_view AS
              SELECT CAST(dblink_tok(t1.dblink_db1_info,0) AS MACADDR)   AS id1,
                                 CAST(dblink_tok(t1.dblink_db1_info,1) AS MACADDR)   AS id2,
                           textToBoolean(dblink_tok(t1.dblink_db1_info,2))              AS valid,
                                 CAST(dblink_tok(t1.dblink_db1_info,3) AS TIMESTAMPTZ) AS modify_time
              FROM (SELECT dblink('hostaddr=127.0.0.1 port=5432 dbname=db1 user=postgres',
                         'SELECT id1, id2, valid, modify_time FROM db1_info') AS dblink_db1_info) AS t1;

Periodically we update information in the second database based upon the modify time from this view. This is the query that is run from a stored procedure. I've separated it out of the stored procedure because running it by hand in psql has the same problem.

SELECT v.id1, v.id2, v.valid, v.modify_time
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;

Until recently this has worked perfectly.  We started increasing the number of records in these tables and now we're running into the problem where the SELECT statement does not return. I'm not talking about a lot of records. Both tables in the query only have about 9,000 records each.

I can do a SELECT COUNT(*) or SELECT * on the view and it comes back instantly. I can SELECT * and ORDER BY modify_time and it comes back instantly. As soon as I try to join the view to another table the query takes 10 to 20 minutes to come back.

NOTICE:  QUERY PLAN:

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

EXPLAIN

If I create a temporary table by selecting everything from the view and then do the join it comes back instantly.

NOTICE:  QUERY PLAN:

Merge Join  (cost=69.83..526.68 rows=1000 width=40) (actual time=109.03..247.78 rows=9353 loops=1)
  ->  Index Scan using allowed_station_view_pkey on allowed_station_view av  (cost=0.00..426.88 rows=9353 width=20) (actual time=0.03..41.93 rows=9353 loops=1)
  ->  Sort  (cost=69.83..69.83 rows=1000 width=20) (actual time=108.95..120.77 rows=9353 loops=1)
        ->  Seq Scan 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)
Total runtime: 261.97 msec

EXPLAIN

Am I misusing dblink?

Dawn Hollingsworth
AirDefense, Inc. --=-DiLhJBbdDIAeVGVY7FuY--