Alex - <aintokyo@hotmail.com> writes:
> 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
actuallyget 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-2minutes.
> 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
moredata 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
firstone of the next one, but yet it will start crawling again.
What this sounds like is it's fast when most of the data has been
swapped in to kernel disk cache, and slow when the data actually has to
be read from disk. Probably the reason it's slow in the morning is
there are some unrelated tasks that run overnight and cause memory to
get filled with all their data instead of the PG tables. You could
check this theory by watching with vmstat or similar tool to see how
much actual disk I/O is happening in the slow and fast cases.
regards, tom lane