Optimize sql query on postgresDB

Question

I have around 700 tables in my DB.When I run the script below, it takes almost 1 hour. How can I optimised this query?

Database: Postgres

DO $$
DECLARE
    tables_list CURSOR FOR
        select distinct t_name, t_schema from information_schema.columns 
 where column_name = 'deleted_flag' 
    and t_schema='customer' and t_name not like 'v_%';
BEGIN
    FOR t_record IN tables_list LOOP
        EXECUTE 'update ' || t_record.table_schema || '.' ||      t_record.table_name || ' set deleted_flag=false';
    END LOOP;
end;
$$;

At the end, all the tables in this schema should have this field deleted_flag as false. I have to run this script very often on production environment. Grateful if someone can help how to optimise this script.

Do you think if add a check in the where clause,

EXECUTE 'update ' || t_record.table_schema || '.' ||      t_record.table_name || ' set deleted_flag=false where deleted_flag=true';

the execution time will be less?

Strong advice not to use string concatenation. Use theFORMAT() function instead (which handles mixed case and embedded whithespace in identifiers) — wildplasser
– wildplasser, Commented May 19, 2018 at 13:06

Laurenz Albe · Accepted Answer · 2018-05-19 12:43:12Z

1

There is no way to make this really efficient.

Here are a few pointers what you can do to make it faster:

You should definitely add WHERE deleted_flag to the query to avoid unnecessary updates.
If there are only few rows where deleted_flag = true, you can create a partial index like this:
```
CREATE INDEX ON atable ((1)) WHERE deleted_flag;
```
If there are too many rows to make a partial index useful, create the tables with fillfactor = 50 and make sure that there is no index on deleted_flag.

Then you can enjoy HOT update which is much cheaper.
Set max_wal_size high enough that you don't end up with too many checkpoints.
Get enough RAM so that the whole database fits in memory.
Get fast storage.

But I think that your design is weird, and the best solution would be to find a way to avoid these regular updates.

edited May 19, 2018 at 12:43

answered May 18, 2018 at 18:53

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sticky bit Over a year ago

follfactor? You mean fillfactor, don't you?

Laurenz Albe Over a year ago

I meant foolfactor. No, seriously - fixed. Thanks.

Collectives™ on Stack Overflow

Optimize sql query on postgresDB

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related