On Thu, May 3, 2012 at 12:07 PM, Eyal Wilde <eyal@xxxxxxxxxxxxxxxx> wrote: > 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? let's see the query plan...when you turned it off, did it go faster? put your suspicious plans here: http://explain.depesz.com/ > 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. yeah. well, your query was an insert? that would naturally result in blocks out. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
