I have a database with 200k rows (one of this column is a jsonb data type column), i'm using this database to store some various devices informations (currents, pressure, etc...) and i'm using this table to get the respective measurements of a specific device using a time range. When the database is small (like 50/70k rows) everything works fine, the cache hit ratio is positive and around the 99%.
When the database grows, like now, the cache hit ratio will decrease dramaticaly and goes to negative, as shown below:
But, as you can see, the index on the table it seems to be in a good state (despite the low number of rows contained in the index):
The database size is around 200MB and the modbus table has the size of 156Mb, my host has a 1GB Ram with 2CPU.
Here there is one of the slowest query, for only 54/60 results, it gives 10 seconds of execution:
Please, give me some advices...