1

I have a scenario like i have a two DB, one resides in remote server and other resides local. Now i have to fetch the records from remote DB when new records are inserted on tables (in remote DB) and insert into local DB (Both DB have a same table structure).

I have many tables on it and for each and every table i have to do this activity. For this we wrote a function like on below flow.

  1. create dblink for remote server and map user.
  2. create foreign tables
  3. get the new records count from remote table through foreign table option and store it in table (contain table_name and no of records).
  4. And based upon this table if i have a count >0 then call another function which sends query to fetch records from remote server and create insert query for local DB (record wise inserting) execute insert query.
  5. Then create loop for log table to generate inserted records details and insert it into log table.

My Questions:

  1. Since my team is asking to reduce the total time i increased the following parameters in postgresql.confg file (windows machine) as per 'pg-tune' suggested,

shared_buffers work_men maintenance_work_men effective_cache_size

but i cant find any performance improvement.

What i'm missing here? can any one suggest on this?

  1. To find where my function taking more time to perform i used the module 'auto_explain' (for session) and noted the execution plan,

    auto_explain settings

load 'auto_explain'; set auto_explain.log_min_duration=0; set auto_explain.log_nested_statements=ON; SET auto_explain.log_analyze = true;

as observed i added the index on required field. Sample is given below,

log Bfr adding index

2014-12-09 10:37:33 IST LOG: duration: 129.007 ms plan: Query Text: SELECT count() FROM res_users_ft j0 WHERE j0.write_date > '1970-01-01 00:00:00' Aggregate (cost=178.27..178.28 rows=1 width=0) (actual time=129.001..129.001 rows=1 loops=1) -> Foreign Scan on res_users_ft j0 (cost=100.00..175.42 rows=1138 width=0) (actual time=128.882..128.887 rows=29 loops=1) 2014-12-09 10:37:33 IST CONTEXT: SQL statement "SELECT count() FROM res_users_ft j0 WHERE j0.write_date > '1970-01-01 00:00:00'" PL/pgSQL function fn_get_update_row_counts() line 36 at EXECUTE statement SQL statement "SELECT fn_get_update_row_counts()" PL/pgSQL function fn_pull_hq_to_hqstag_batch_process() line 7 at EXECUTE statement

Log After adding index

2014-12-10 10:46:29 IST LOG: duration: 0.762 ms plan: Query Text: SELECT count() FROM res_users_ft j0 WHERE j0.write_date > '1970-01-01 00:00:00' Aggregate (cost=178.27..178.28 rows=1 width=0) (actual time=0.759..0.759 rows=1 loops=1) -> Foreign Scan on res_users_ft j0 (cost=100.00..175.42 rows=1138 width=0) (actual time=0.746..0.750 rows=29 loops=1) 2014-12-10 10:46:29 IST CONTEXT: SQL statement "SELECT count() FROM res_users_ft j0 WHERE j0.write_date > '1970-01-01 00:00:00'" PL/pgSQL function fn_get_update_row_counts() line 36 at EXECUTE statement SQL statement "SELECT fn_get_update_row_counts()" PL/pgSQL function fn_pull_hq_to_hqstag_batch_process() line 7 at EXECUTE statement

But overall time is still remaining the same Since time got reduced on above log.

What i'm missing here? can any one plz suggest on this?

And also please suggest me instead of record wise insert can i do bulk insert? if yes suggest me how can i do that without using third party tool?

Note: For inserting records i didn't getting any information (duration) on log. How can i do that to view duration?

thanks in advance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.