Requests with after/before-time have unpredictable postgres query plans.

[urtho]

Problem

Queries with after/before-time have unpredictable performance due to Postgres optimizer issues with joins with block_header table.
Postgres 14 appears to create even worse performing query plans then PG13 when joining over large time ranges with block header table.

Solution

Avoid block_header joins by converting the after/before-time requests to min/max-round requests - eg by using CTE to establish the round number boundaries first.

Urgency

Requests with after/before-time parameters basically do not work at Algonode due to a lot of extra indexes and bad planner guesses even with extended stats.

[gmalouf]

Concrete example for diagnosing:

This works: /v2/accounts/QYXDGS2XJJT7QNR6EJ2YHNZFONU6ROFM6BKTBNVT63ZXQ5OC6IYSPNDJ4U/transactions?tx-type=axfer&before-time=2024-11-22T00%3A00%3A00.000000Z&after-time=2024-11-20T23%3A59%3A59.000000Z&limit=101 This fails: /v2/accounts/QYXDGS2XJJT7QNR6EJ2YHNZFONU6ROFM6BKTBNVT63ZXQ5OC6IYSPNDJ4U/transactions?tx-type=axfer&before-time=2024-11-22T00%3A00%3A00.000000Z&after-time=2024-11-20T23%3A59%3A59.000000Z&limit=102 If I use the next token from the 101 request and a limit of 1 it fails: /v2/accounts/QYXDGS2XJJT7QNR6EJ2YHNZFONU6ROFM6BKTBNVT63ZXQ5OC6IYSPNDJ4U/transactions?tx-type=axfer&before-time=2024-11-22T00%3A00%3A00.000000Z&after-time=2024-11-20T23%3A59%3A59.000000Z&limit=1&next=AQqoAgAAAAAiAAAA