Five merge plans died before the sixth worked, every one measured, none guessed. Hash one side: the planner hashes billions of rows. Force a nested loop: hundreds of thousands of random probes at idle CPU. Every shape failed for the same reason — “same vessel, inside this time window” is not a selective join key; the estimate is enormous no matter which side you hash. The fix was to expand each gap into the hours it covers and join on vessel and hour. Same question, suddenly selective, and the temp files shrank from tens of gigabytes to a few hundred megabytes.
Two other lessons from the same pass. Duplicate harbours — the same real harbour twice, from name-case variants with overlapping geometry — got tombstoned with a merged-into pointer instead of rewriting billions of position rows. A moored yacht at Anholt that had been counted as seventeen separate visits became one stay.
And the quiet one: a nightly merge step did TRUNCATE, then re-inserted a subset of columns. Every column not on that list — all the enrichment a long fix pass had computed — was silently wiped each night, while the aggregates kept producing plausible numbers on top of NULLs. There is now a canary that fails loudly the moment that coverage collapses. Plausible is the most dangerous state a broken pipeline can be in.