[stats-dev] Incremental ogr-verify

[stats-dev at lists.distributed.net]

Ok, here's the fixed query:

Old:
SELECT DISTINCT stub_id, nodecount, id
    INTO TEMP retired_stub_id
    FROM OGR_results o, retire_today r
    WHERE o.id = r.id
;

New:
SELECT DISTINCT stub_id, nodecount, id
    INTO TEMP retired_stub_id
    FROM OGR_results o, retire_today r
    WHERE o.id = r.id
        AND EXISTS (SELECT 1
                            FROM ogr_results o2
                            WHERE o2.stub_id = o.stub_id
                                AND o2.nodecount = o.nodecount
                                AND o2.id = r.retire_to
                        )
;

Now, we only grab a list of nodes where the id being retired into
already returned work.

Actually, this should be faster if we do the DISTINCT first...

SELECT DISTINCT stub_id, nodecount, id
    INTO TEMP retired_stub_id
    FROM (SELECT DISTINCT stub_id, nodecount, id
                FROM OGR_results o, retire_today r
                WHERE o.id = r.id
            ) stubs s, retire_today r
    WHERE s.id = r.id
        AND EXISTS (SELECT 1
                            FROM ogr_results o
                            WHERE o.stub_id = s.stub_id
                                AND o.nodecount = s.nodecount
                                AND o.id = r.retire_to
                        )
;

You'll want to play around with the plan on this (as well as all the
other queries I gave you). I'm pretty sure this will be the fastest
query though, since retire_today should always be small.
-- 
Jim C. Nasby (aka Decibel!)                    jim at nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"