chore: optimize store v3 queries

[Ivansete-status]

Background

It's been detected that store-v3 queries don't respond as quickly as store-v2 and we need to understand why.

Details

This has been originally reported by @richard-ramos . I add some notes shared by him:

./build/query \
   --cluster-id=16 \
   --pubsub-topic=/waku/2/rs/16/32 \
   --start-time=1722424194452637398 \
   --end-time=1722425909559247654 \
   --content-topic=/waku/1/0xa4d9d91b/rfc26 \
   --content-topic=/waku/1/0x42c4e775/rfc26 \
   --storenode=/dns4/store-01.do-ams3.status.prod.status.im/tcp/30303/p2p/16Uiu2HAmAUdrQ3uwzuE4Gy4D56hX6uLKEeerJAnhKEHZ3DxF1EfT \
   --use-legacy=false    <-------- change to true to compare

Legacy:

Page: 1, Record from 1 to 20 (2.401µs)
MessageHash                                                         PubsubTopic       Content Topic             Timestamp                                                     
0xb04336e653d453905f639d7e09b2241c4fb08984243cc0ee9bc1bef364bd90fa  /waku/2/rs/16/32  /waku/1/0x42c4e775/rfc26  1722425876782613943  2024-07-31 11:37:56.782613943 +0000 UTC  
...

Store-v3:

Page: 1, Record from 1 to 20 (1.704460297s)
MessageHash                                                         Content Topic             Timestamp                                                     
0xb04336e653d453905f639d7e09b2241c4fb08984243cc0ee9bc1bef364bd90fa  /waku/1/0x42c4e775/rfc26  1722425876782613943  2024-07-31 11:37:56.782613943 +0000 UTC  
...

storev3 consistently takes more than 600ms to return a page while legacy consistently is < 300ms for the same query

Queries used:

// StoreV3
{
  "request_id": "5a830c991c2adc38ab5a5869905a6b5c0066ff946a6ac98b2503addd0c529937",
  "include_data": true,
  "pubsub_topic": "/waku/2/rs/16/32",
  "content_topics": ["/waku/1/0xa4d9d91b/rfc26", "/waku/1/0x42c4e775/rfc26"],
  "time_start": 1722424194452637398,
  "time_end": 1722425909559247654,
  "pagination_limit": 20
}

// Legacy
{
    "request_id": "a84e648c154c68dae755cd6f1a302e76598d7d0b7b76b040ad7ea7c727baa704",
    "query": {
      "pubsub_topic": "/waku/2/rs/16/32",
      "content_filters": [
        { "content_topic": "/waku/1/0xa4d9d91b/rfc26" },
        { "content_topic": "/waku/1/0x42c4e775/rfc26" }
      ],
      "paging_info": { "page_size": 20 },
      "start_time": 1722424194452637398,
      "end_time": 1722425909559247654
    }
}

[Ivansete-status]

Thanks for submitting that one @richard-ramos !
After double checking it I think we already fixed with #3061. Concretely, the change made in waku/waku_archive/driver/postgres_driver/postgres_driver.nim, which btw you suggested :)

I tested with the following scenario:
From HK boot node, I opened two terminals, each of them performing requests against the same node, with the following script, but only changing the use-legacy param.

while true
do
  timeBegin=1726891200088792846
  timeEnd=1728336393221624141

  ./build/query \
      --cluster-id=16 \
      --pubsub-topic=/waku/2/rs/16/32 \
      --storenode=/dns4/store-02.ac-cn-hongkong-c.status.staging.status.im/tcp/30303/p2p/16Uiu2HAmU7xtcwytXpGpeDrfyhJkiFvTkQbLB9upL5MXPLGceG9K \
      --content-topic='/waku/1/0x76f3a801/rfc26' \
      --content-topic='/waku/1/0x0ad6694a/rfc26' \
      --content-topic='/waku/1/0x42c4e775/rfc26' \
      --content-topic='/waku/1/0xbc0a6d4a/rfc26' \
      --content-topic='/waku/1/0x05d53336/rfc26' \
      --content-topic='/waku/1/0x6bd4a29d/rfc26' \
      --content-topic='/waku/1/0xf3a9da50/rfc26' \
      --content-topic='/waku/1/0x1a96e4e1/rfc26' \
      --content-topic='/waku/1/0x4b6ca0f6/rfc26' \
      --start-time=${timeBegin} \
      --end-time=${timeEnd} \
      --pagesize=100 \
      --use-legacy=true

  echo "=================================="
  echo ""
done

With that, I've got the impression that it works slightly better with store-v3 than with store-v2.

store-v2 snippet:

store-v3 snippet: