1

The following query is run by an API. Individually it takes almost no time at all to run because the variables are intentionally limited to a single identifier and a small window. Here is the query:

      SELECT
        raw_data->'foo'->0->>'time' as timestamp
      FROM dbo.raw_data
      WHERE raw_data->'foo'->0->>'number' = '${identifier}'
        AND raw_data->'foo'->0->>'time' > '${queryStartDateEpoch}'
        AND raw_data->'foo'->0->>'time' < '${queryEndDateEpoch}'
      ORDER BY raw_data->'foo'->0->>'time' DESC
      LIMIT 1

One query will run for every unique system number my API gets supplied.

I expect this API to run this query anywhere from 40 to 100 times at once. The issue I am seeing is that the first few queries run in moments, but then after that point the performance drops off and I end up waiting 20-30 minutes for all 40+ queries to resolve.

Can anyone explain why PostgreSQL would slow down like that? Thank you.

I did not set up the docker server for the PostgreSQL database and have limited background knowledge about that. I imagine there is a limit to the number of queries you can run, but I am not clear how to identify the bottlenecks.

BrianH
  • 92
  • 9

1 Answers1

0

It is very probable that you require indexes for WHERE's filters, I expect that you are using JSONB data type for raw_data, please check the doc:

https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

and check this answer:

https://stackoverflow.com/a/36076895/8308381

Anthony Sotolongo
  • 1,395
  • 2
  • 9
  • 17
  • The database is already indexed. The queries run quickly. The problem is that when 40 or more of them are called in rapid succession, the running time of each increases dramatically. – BrianH Apr 09 '21 at 14:02