How to speed up Postgres queries on Retool?

Queries and Resources
1

I have a very simple Postgres query to populate options from a Multiselect element as the user types:

image
image518×864 53.8 KB

The query itself basically looks like

SELECT 
    name
   , jobs
   -- , and other attributes
FROM company
WHERE 
    name ~* {{ companyMultiSelect.inputValue }} 
    OR url ~* {{ companyMultiSelect.inputValue }}
ORDER BY jobs DESC NULLS LAST

company is a pretty big table with close to 1M rows and there are trigram indices on both the name and url columns so that text queries on those are fast.

I added an explain analyze before so that we can see that the query is indeed fast:

image
image2394×982 240 KB

But as you can see in the left panel, it still took almost 2 seconds to run, and many times it takes even longer:

image
image688×548 61.7 KB

Running a similar query on psql is very fast and as I understand, almost real-time:

image
image2874×550 151 KB

Is there a way to make Retool faster when making Postgres queries?

2

Pasting here the response @Kabirdas gave me via support because I think it could be useful to other users:

If you haven't already you might want to check out our docs on query runtimes as they give a bit of context as to what each of those categories means. In your case, I think this might be due to some latency in communicating with our backend. Our servers are located in the pacific northwest of the US, so to execute your query you'd need to

  1. Send the request from your browser to our backend servers
  2. Have our backend servers send the request to your DB
  3. Have your DB send the response to our backend
  4. Have our backend send the response to your browser

Since it looks like you're located pretty far from our servers steps 1) and 4) can add significant latency, and if your DB server is also located far away there will be more latency in steps 2) and 3).

For some users, this is a reason to consider using a self-hosted instance of Retool since that lets you control where the backend is hosted and can cut down on latency.

In my case I'm in Europe and my DB is in Europe, so that'd mean the data crossing the Atlantic 4x before I get a response (Europe -> US West -> Europe -> US West -> Europe).

A possible solution besides self-hosting Retool would be running a replica of my DB hosted in the US, but for now I'll leave it like it is now, not worth the hassle IMO.

1 Like