I have a number of long-standing apps and have started to experience significant Postgresql query performance issues. I'm connecting directly to a Google Cloud Postgres database.
An example: I have a single query that does a SELECT * from a view. It now takes between 15-30 seconds to run, every time.
If i run this from Navicat on my desktop is takes 0.57 seconds.
If I create a new resource query, and enter exactly the same SQL, it takes about a second. I've checked all the other query setting (timeouts etc) and there are no differences, it's just the fact that it's a new query that fixes it.
Hello there @domjammoo, can you confirm if the old queries and the new ones you were testing are using the same resource? If they are, could you also share the resource ID? You can find the resource ID by following this doc: Dynamically query resources | Retool Docs
Yep they're definitely using the same resource. I have some vague feeling that it might be because these queries are really old - I am a long time Retool user - and they may still be hanging on to some legacy / deprecated bit of code somewhere on the Retool side.
This is the resource id: a54dfe6d-48cd-45d3-a975-bef1a71c8684
HOWEVER:
Every app I use uses the same resource.
If I create a new query, against the same resource, and copy paste the exact same query definition, in the same app, and make sure all of the settings are the same, the new query runs in sub one second, so it's probably not the resource itself;
When editing the 'slow' version of the query, when I click 'Test' it runs in sub one second, but when I click 'Run' it takes 10 seconds;
When I look in the debug timeline, whilst it is running, the execution duration bar keeps extending up to 10+ seconds, but as soon as it completes, it 'snaps back' to show ~1 second
It obviously didn't use to do this
It is querying a very very simple view that joins two tables on indexed primary keys and only returns about 80 rows so it should be lightning fast
*** EDIT ****
Actually I've just found something out. The data is displayed in a very simple table. The table is an 'old' table, the deprecated type. When I unlink the table, it runs fine. So something about the tables consumption of the data is actually blocking the reported execution time of the query. Does this mean I have to go through all of my apps and replace deprecated tables? The upgrade path is not straightforward as the new table breaks many of the features of the old table (such as the change in the use of recordUpdates)
Hey @domjammoo, thanks for taking the time to test this and share your findings. Based on what you wrote, I suspect the old Table is the issue. I highly recommend upgrading to the new Table component since the old one is deprecated and we generally don’t ship updates for deprecated components.
If you run into trouble upgrading, I’d suggest joining us for Office Hours so we can help you out. We have an upcoming Office Hours session Sept 4 at 2pm EST, where you can get help live from the Retool team and other developers. We host these Office Hours twice a week!
Also, just out of curiosity, when you’re referencing the same query, how are you linking the result of the query to the table? Using tableLegacy.setData in an event handler is usually less performant compared to query1.data. Which one of those are you using?
