MIME-Version: 1.0
References: <CAEDh4nzEm2e=yFnC3UWjMVknjMvHsSX0nVtYdVZJK9NAMMaKVg@mail.gmail.com>
 <CAKAnmm+vumTRt8WB0OLsy7cVbYqdPW1m1EEOT26MCFPUd0JcOg@mail.gmail.com>
 <CAEDh4nxpTobNoV8=6hqJm3gP2i6-=RvD9R9d1zh=6VrOta3gSA@mail.gmail.com>
 <CAKAnmmKe569wZRFAEmzpxHPJeVE340iP0Qf3G+sJMgf7HgedRQ@mail.gmail.com> <CAEDh4nyVW-Q_-F2S8m5o+uVh53ea6FCSLXYJEqBu_8bRZy5YYg@mail.gmail.com>
In-Reply-To: <CAEDh4nyVW-Q_-F2S8m5o+uVh53ea6FCSLXYJEqBu_8bRZy5YYg@mail.gmail.com>
From: Bernice Southey <bernice.southey@gmail.com>
Date: Wed, 17 Dec 2025 17:39:59 +0000
Message-ID: <CAEDh4nyG1kGBk7eN3WPEKfhupADbzOxoQvdw+G5f-e1ubXXvOg@mail.gmail.com>
Subject: Re: Advent of Code Day 8
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAEDh4nyG1kGBk7eN3WPEKfhupADbzOxoQvdw%2BG5f-e1ubXXvOg%40mail.gmail.com>
Precedence: bulk

It's slow at 20 seconds, but I'm pleased I finally found a good enough
way to use tables for day 8. Afterall, the reason I tried AoC in
postgres is because I really like table logic. By swapping out two
temp tables and doing insert only, I can avoid the update MVCC bloat
that wrecked my previous attempt. It was very educational watching the
loop speed degrade to a crawl after a thousand update runs, even
though the table never got bigger than 1000 rows.

I copied the input into d8(t text).

create temp table w1(c int, b text);
create temp table w2(c int, b text);

do $$
declare r record;
begin
--loop through the connections in closest order
for r in (with j as (
    select row_number() over () r, t,
        split_part(t, ',', 1)::int8 x,
        split_part(t, ',', 2)::int8 y,
        split_part(t, ',', 3)::int8 z
    from d8)
    select row_number() over(
        order by (j.x-j1.x)^2 + (j.y-j1.y)^2 + (j.z-j1.z)^2) i,
        j.t b1, j1.t b2, j.x * j1.x s from j, j j1 where j1.r > j.r) loop

    --add the two boxes from the current connection
    insert into w1 values (r.i, r.b1), (r.i, r.b2);

    --connect all the boxes in the circuits of these two boxes
    insert into w1 select r.i, w3.b
    from w1 join w2 on w1.b = w2.b join w2 w3 on w2.c = w3.c;

    --keep all the existing boxes with their current circuits
    insert into w1 select * from w2;

    truncate w2;
    --get the latest circuit per box
    insert into w2 select distinct on (b) * from w1 order by b, c desc;
    truncate w1;

    --the circuit is complete when all the boxes are in the current circuit
    if (select count(*) from w2 where c = r.i) = 1000 then
        raise notice '%', r.s;
        exit;
    end if;
end loop;
end
$$;