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
queryin the database of the first. That way you can perform the query in one database, which will only have to move the
rowsneeded 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
databaseon 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.