Goal: Bulk update from a Table (New) component in reasonable time
Steps: I have a Table(new) component with a few updatable columns. I'm using the Save Actions plug-in and GUI bulk update via a primary key. The query always takes around 8.5 seconds to complete even with only one cell change
Details: I have two update queries connected to a Postgres db. One is the single GUI "update an existing record" using a filterBy on the primary key in the table. This query runs fine (subsecond as you'd expect). Doing the same update with the GUI "Bulk update via a primary key" using the same primary key takes about 8.5 seconds. I'm trying to figure out why there is a such a big discrepancy in how long the updates take when they essentially are doing the same thing.
Is there some way to see more details of what happens with these two types of gui queries? I can't find the sql anywhere, and the traces don't show any details of where the delay might be. I'm just looking for a more in-depth view of what is happening in the gui queries
Hey @BrianP ,
I understand your problem . You have to do like:
PostgreSQL might be choosing different execution plans for the single update and the bulk update queries. You can use the EXPLAIN ANALYZE command to see the execution plan for both queries and compare them.
Steps to Diagnose and Mitigate the Issue:
Compare Execution Plans:
EXPLAIN ANALYZE UPDATE your_table SET column = value WHERE id = single_id;
EXPLAIN ANALYZE UPDATE your_table SET column = value WHERE id IN (bulk_ids);
Check for Locks:
Use the following query to check for locks during the execution of your updates:
SELECT * FROM pg_locks WHERE relation = 'your_table'::regclass;
So I was finally able to run a trace to see what sql retool was sending to the db.
It turns out that before the updates run, it runs this (unique_key being the primary key):
select count(distinct "unique_key") as "count", count(*) as "totalcount" from "my_table" where "unique_key" is not null
Then it runs the actual updates which are very fast.
And then it runs that same Select statement again.
Each time it's running that select statement, it's taking 4.5 seconds, so about 9 seconds in all.
Why would retool send those select statements to the db? It's slowing the updates down to a crawl? Or is there some option that is causing that?