Slow concurrent processing

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