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:

enter image description here

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):

enter image description here

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:

enter image description here

Please, give me some advices...


  • 2
    Could you please provide your queries as text and give us the output for an explain analyze ? – Arkhena Feb 4 '19 at 9:36
  • Here is the Explain of the query and the query itself: explain.depesz.com/s/7jrN pastebin.com/Yk3fGabQ – VirtApp Feb 4 '19 at 12:03
  • @Arkhena, did you noticed something strange in my query? – VirtApp Feb 4 '19 at 13:49
  • 1
    First optimization rule : don't do it. Second optimization rule : don't do it yet (for experts only). If your query runs under 250ms, it probably doesn't need optimization. – Arkhena Feb 4 '19 at 14:08
  • 1
    ...@Arkhena, i think that i've found the issue: my django app, actually it's doing a query (with the selection of the json key) for every datapoint, i mean, if i have ten datapoint (ex.: VLN1, VLN2, VLN3, IL1, IL2, IL3, PL1, PL2, PL3, etc...) my code will execute a query with the respective subselect for each of them: this is extremely inefficient. Now i'm working to do a single query with much sub-select as the number of my datapoints, in order to execute only one query. i think that the problem is definitively this. – VirtApp Feb 4 '19 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.