Indexer Lag in Dev Mode

"The behaviour I'm seeing is that intermittently, but consistently (multiple times every test run) indexer will wait for about 17s (almost exactly) until the transaction that's being waited for appears. The rest of the time it will happily appear sub-second. This makes me think there some sort of deadlock or delay somewhere."

Quoting @robdmoore:

> I've recently found that there is an intermittent, but consistently occurring and significant performance issue with indexer when running the automated tests for https://github.com/algorandfoundation/algokit-subscriber-ts.
>
> There are some tests in that library that use the following function in a series of tests (e.g. filters.spec.ts and events.spec.ts): https://github.com/algorandfoundation/algokit-subscriber-ts/blob/main/tests/filterFixture.ts#L29. Namely the [call to `waitForIndexerTransaction`](https://github.com/algorandfoundation/algokit-subscriber-ts/blob/main/tests/filterFixture.ts#L34).
>
> This method is defined in algokit-utils here: https://github.com/algorandfoundation/algokit-utils-ts/blob/main/src/testing/fixtures/algorand-fixture.ts#L122 and consists of these two underlying methods: https://github.com/algorandfoundation/algokit-utils-ts/blob/main/src/testing/indexer.ts#L12 and https://github.com/algorandfoundation/algokit-utils-ts/blob/main/src/indexer-lookup.ts#L20.
>
> The behaviour I'm seeing is that intermittently, but consistently (multiple times every test run) indexer will wait for about 17s (almost exactly) until the transaction that's being waited for appears. The rest of the time it will happily appear sub-second. This makes me think there some sort of deadlock or delay somewhere.
>
> I've looked at logs in algod, conduit, indexer and postgres and attached a profiler of sorts of postgres, but haven't seen anything conclusive yet.
>
> Two things I have found:
>
> * The problem seems a lot worse with recent versions of algod/indexer/conduit
> * The problem was even worse still when I used this code (the previous code in this commit) instead: https://github.com/algorandfoundation/algokit-subscriber-ts/commit/0ec773573f192c3a2b375445c889a51252350690
>

_Originally posted by @robdmoore in [#1457](https://github.com/algorand/indexer/issues/1457#issuecomment-2018148431)_