guess what:
after reducing bo (blocks out) to ~10% by using a ramdisk (improving overall performance by ~15-20%), i now managed to reduced it to ~3% by .... removing the "analyze temp-table" statements.
it also :
reduced b (Process which are waiting for I/O) to zero
reduced wa (percentage of time spent by cpu for waiting to IO) to zero
and reduced id (cpu idle time percent) to be 4 times less.
r b swpd free buff cache si so bi bo in cs us sy id wa st
8 0 0 6144048 237472 485640 0 0 0 40 4380 3237 79 5 16 0 0
8 0 0 6138216 238032 485736 0 0 0 40 4741 3506 93 7 0 0 0
8 0 0 6125256 238276 486484 0 0 0 2709 4801 3447 92 7 1 0 0
7 0 0 6119400 238376 485792 0 0 0 32 4854 4311 93 6 1 0 0
5 0 0 6105624 238476 486172 0 0 0 364 4783 3430 92 7 1 0 0
5 0 0 6092956 238536 485384 0 0 0 416 4954 3652 91 8 2 0 0
unfortunately, this time there was no significant performance gain. ):
i afraid now there are certain statements that do not use an optimal query-plan. these statements looks like:
insert into temp-table1 (value) select table1.f1 from table1 join temp-table2 on table1.recid=temp-table2.recid where table1.f2 in (x,y,z);
temp-table2 never contains more then 10 records.
there is an index on table1: recid,f2
previous tests showed that the query-optimizer normally chose to do hash-join (i.e: ignoring the index), but once we did "analyze temp-table2;", the index was used. i read somewhere that the optimizer's assumption is that every temp-table contains 1k of records. i believe that is the reason for the bad plan. we tried to do "set enable_hashjoin=false;", but it did not seem to be working inside a function (although it did work independently). what can we do about that?
another thing i found is that a sequence on a temp-table is being stored on the current tablespace, and not on the temp_tablespace. would you consider this as a bug?
anyway, i found a way to not using any sequences on the temp-tables. but this did not change the bo (blocks-out) figures.
merlin,
about the Hint Bits. i read this article: http://wiki.postgresql.org/wiki/Hint_Bits
as far as i understand, this is not the case here, because i ran the test many times, and there were no DML operations at all in between. so i believe that the hint-bits are already cleared in most of the tuples.
Thanks again for any more help.
