Hi all,
We have one table with list of "records for processing"...
We loop trough that table and call one long runing function:
do_the_math_for_record(record_id)
which use different tables for select related rows for input record_id, do some calculations and insert results in two tables...
and we have made 1 function process_all_records()
what simply does: SELECT do_the_math_for_record(record_id) FROM records_for_processing
When we run that function - it last about 4 minutes...
There are about 300 rows in records_for_processing... we have logged the time on the beginning of do_the_math, and the time in end of do the math... and noticed that processing each row, last between 0.5 to 2 seconds...
so our do_the_math looks like:
PERFORM log_time(record_id, clock_timestamp(), 1)
PERFORM do_the_math_and_save_results(record_id);
PERFORM log_time(record_id, clock_timestamp(), 2)
Then we thought, if we take all "records for processing" and process each in separate connection - it should last longer...
but - got worse result! (using 30 concurrent connections...)... about 7 mins...
if we reduce concurrent connections on 10 - we got result in approx the same time as sequential processing...
but - if replace do_the_math_and_save_results with pg_sleep(1); To simulate long running function so processing each row - last 1 sec...
Sequential processing last as expected 300 seconds!
Concurrent processing last faster with higher number of concurrent connections - about 30 seconds with 30 connections! (much faster - and expected...)
however, if we return our: do_the_math_and_save_results - we can't get better results in concurrent processing...
with higher number of conccurent connections - result is worse... also we have noticed that for some records difference between end_time and start_time si even longer than 1 min - but it is random - not always on the same id... i.e. in this concurrent run lasts 1 min - in next 1 sec - but some other takes about 1 min...
Any idea - why? :)
It says to me - that there is somewhere lock on some tables - so probably our concurrent connections wait - to other finish... but I cant figure out: what and why...
do_the_math_and_save results - selects data from 10 other tables, calculates something, and results inserts in other tables...
there are about 3 tracking tables with (record_id - other data...... and about 7 settings tables what we join to tracking tables to get all info...), then do the math with that info - and insert results..
we don't do any update... (to have possibility two connections want to update the same row in the same table)
data from tracking_tables - should be separate sets of data for two differenet record_ids...
(joined rows from settings tables could be common - for two sets of different record_id)
but - even they are the same set - SELECTs should not lock the rows in tables...
There are places where we do:
INSERT INTO result_table (columns)
SELECT query (tracking and settings tables joined)
Is there a chance it does some lock somewhere?
can above query be run "concurrently"?
Many thanks,
Misa
