pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery Against a dblink View Takes Too Long to Return
3+ messages / 2 participants
[nested] [flat]
* Query Against a dblink View Takes Too Long to Return
@ 2003-02-28 12:54 Dawn Hollingsworth <dmh@airdefense.net>
0 siblings, 0 replies; 3+ messages in thread
From: Dawn Hollingsworth @ 2003-02-28 12:54 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Query Against a dblink View Takes Too Long to Return
@ 2003-03-03 16:58 Dawn Hollingsworth <dmh@airdefense.net>
2003-03-04 02:43 ` Re: Query Against a dblink View Takes Too Long to Return Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Dawn Hollingsworth @ 2003-03-03 16:58 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query Against a dblink View Takes Too Long to Return
2003-03-03 16:58 Query Against a dblink View Takes Too Long to Return Dawn Hollingsworth <dmh@airdefense.net>
@ 2003-03-04 02:43 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-03-04 02:43 UTC (permalink / raw)
To: Dawn Hollingsworth <dmh@airdefense.net>; +Cc: pgsql-sql
Dawn Hollingsworth <dmh@airdefense.net> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-03-04 02:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-28 12:54 Query Against a dblink View Takes Too Long to Return Dawn Hollingsworth <dmh@airdefense.net>
2003-03-03 16:58 Query Against a dblink View Takes Too Long to Return Dawn Hollingsworth <dmh@airdefense.net>
2003-03-04 02:43 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox