Raghavendra Rao J S V wrote:
> Device id column logic has changed in my organization.
> Therefore, I need to modify all the old device id values to new device id value
> of the tables which contains the device id column.
> Old device id and new device id columns are mapped in “old_new_deviceids” table.
>
> There are twenty tables which contains device id column.
> Therefore I need to modify the device id’s in all those twenty tables based on “old_new_deviceids” table .
> Each and every table will contains around 2Lakhs records.
That is a sign of a bad design. Any value that can change should only
occur once in the database.
200000 rows is not a lot; you should stick with international measures
to make yourself understood.
> I have created a dynamic procedure using EXECUTE FORMAT ,which accepts table name and column name as input parameter as below.
>
> CREATE OR REPLACE FUNCTION pop_endpoints_with_new_deviceid()
> RETURNS void
> AS $$
[...]
> PERFORM pop_new_deviceid_for_table(‘Table_Name1','deviceid');
[...]
> $$ LANGUAGE plpgsql;
>
>
> CREATE OR REPLACE FUNCTION pop_new_deviceid_for_table(p_table varchar,p_column varchar)
> RETURNS void
> AS $$
[...]
> v_select:='SELECT distinct t2.deviceid_old,t2.deviceid_new
> FROM '|| p_table ||' t1,endpoint_deviceids_barediscovery t2
> WHERE t1.'||p_column||'=t2.deviceid_old
[...]
> FOR rec IN EXECUTE v_select LOOP
[...]
> EXECUTE FORMAT('UPDATE %I set %I = %s where %I=%s',p_table,p_column,rec.deviceid_new,p_column,rec.deviceid_old);
[...]
> END LOOP;
[...]
> $$ LANGUAGE plpgsql;
[...]
> Sometimes “select pop_endpoints_with_new_deviceid()” is taking just 5 minutes
> and some times more than 2hrs 25 minutes. how to narrow down the issue
The problem is clear. Rather than changing all rows with a single UPDATE
statement, you perform one UPDATE per row.
> How to tack the time taken by each function in postgres?
You could use pg_stat_statements with pg_stat_statements.track = all
or use PL Profiler: https://bitbucket.org/openscg/plprofiler
Yours,
Laurenz Albe
