Re: Submit query using dblink that hung the host

On 14 Jun 2012, at 20:25, Merlin Moncure wrote:

>> CREATE OR REPLACE VIEW missing_archiveset_in_mds_ops
>> (filename, esdt, archiveset) AS
>> select * from dblink('host=ops_host port=4001 user=omiops dbname=omiops',
>> 'select filename, esdt, archiveset from
>> filemeta_archiveset join filemeta_common using(fileid)
>> join file using(fileid)') as t1(filename text,esdt text,archiveset int)
>> where (filename, esdt, archiveset) not in (
>> select filename, esdt, archiveset
>> from dblink('host=ops_host port=4002 user=omiops dbname=metamine',
>> 'select filename, esdt, archiveset from
>> file_archiveset join filemeta using(fileid)
>> join filename using(fileid)') as t2(filename text,esdt text,archiveset
>> int));
>
> In the meantime, restructure both dblinks to gather the data into
> separate local tables (temporary if you can wing it), then create
> indexes in advance of the join.

I was thinking along those lines, with the difference that I'd create a temp table with the data from the 2nd dblink query in the database of the first. That way you can perform the query in one database, which will only have to move the rows needed for the end result.

I also notice that you don't use any data from the local database in that query at all. Perhaps you could query the database on port 4001 instead? That would seem to make more sense for this particular query.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.