PL/Perl Performance Problems - Mailing list pgsql-general

I am experiencing some strange behavior when executing a not too complicated pl/perl function.

The Function is not too complicated. It does...

1. Selects about 20 Records from Table A (

   - loops though the list and deletes in total about 50k records in Table B

2. For each record form Table A it then selects Records from Table C

   - loops through these records about 50K in total

   - for each runs a query 3 Tables, 10-20M records

   - inserts a record in Table B .. about 50K

3. Returns some stats on the whole operation (100 records).

I am using PL/Perl for this and everything runs pretty well.

In a single execution i.e. if I only process 1 records the whole process is done within a few milliseconds.

The system has 16BG of Memory, and fast disks.

Now here is what I noticed.

a) if I run it in the morning, processing starts very slow, but after a few thousand records it will speed up until I actually get about 100 records processed per millisecond.

b) it sometime takes about 5-10k records till i really get up to speed. meaning the first few hundreds can take up to 1-2 minutes.

c) if i run the same job a few hrs later (we run it twice a day) it generally runs much faster. Even though we added more data to one of the big tables it selects from.

d) this however starts again the next day. ( not much data has been changed between the 2nd run of the day and the first one of the next one, but yet it will start crawling again.

e) sometime the 2nd run of the day can also be slow and even though the data in the system does not change by a large margin, run times of the jobs vary by a large amount. from 17-50 minutes.

Here are my questions:

A) I am running the Auto vacuum. Is it possible that this for some reason slows down the system?

B) Are the query planner stats re-set over night i.e. date change? This behavior is consistent. i.e. Every morning the processing is slow, afternoon generally much faster.

C) Does pl/perl have some memory issues?

D) If i run above job processing 50 records, but stop the process after 10K, then run it again it will run fast for the first 10K then slow down.

My assumption is that the query planner keeps the stats for a particular record based on a time stamp (used in a select) and every day it will forget about that. Is there a way to let the query planner keep stats for a function permanently?

Thanks for any advise.

Alex