I am using dbLink library to synchronize tables in two databases. Below are two identical in terms of their results queries, one is using NOT EXISTS and another NOT IN. The later one is 100 time slower then the former one according to EXPLAIN. However, the former one throws an interesting exception every time I run it. Interestingly, it works fine if the local table is empty. Any ideas why it happens?
replica=> SELECT objectid
FROM activity
WHERE
NOT EXISTS(SELECT remoteid
FROM (SELECT CAST (dblink_tok(t1.dblink_p, 0) AS int8) AS remoteid
FROM (SELECT dblink(dblink_settings,'SELECT objectid FROM activity') AS dblink_p
) t1
) a1
WHERE remoteid=activity.objectid);
ERROR: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
replica=> SELECT objectid
FROM activity
WHERE
objectid NOT IN (SELECT remoteid
FROM (SELECT CAST (dblink_tok(t1.dblink_p, 0) AS int8) AS remoteid
FROM (SELECT dblink(dblink_settings,'SELECT objectid FROM activity') AS dblink_p
) t1
) a1
);
objectid
----------
(0 rows)
