Re: [Netdisco] Strange backup job errors

[Brian De W. <br...@bl...>]

On Thu, 11 Nov 2010 09:56:27 +1100
Ben Carbery <ben...@gm...> wrote:

> 
> This query was eventually dieing after around several hours. Just by
> deleting old entries in the table the performance has improved. At around
> 300,000 records it's tolerable.
> 
> Leaving the excess records aside, I am wondering if this problem is due to
> the version of postgres we are running. I would really appreciate it if
> someone out there has a >8.1 server they can run the EXPLAIN on and copy the
> list.
> 

Actually, I already diagnosed and corrected this issue, although the
fix hasn't been committed.  In IRC, we worked out a replacement query
that's much faster but functionally equivalent:

Replace:
    my $rows = sql_do("DELETE FROM node_ip WHERE mac NOT IN (SELECT mac FROM node)");
With:
    my $rows = sql_do("DELETE FROM node_ip WHERE mac IN (SELECT DISTINCT mac FROM node_ip LEFT JOIN node USING (mac) WHERE port IS NULL)");

In my case, the problem surfaced when my cron jobs in the morning hung
on queries because they couldn't use the node_ip table while the delete
query had it locked. Sorry for not recognizing that was the issue you
were having sooner.