I will use this thread to throw a question I threw time ago but was
unresolved, I think it is very close with this thread, cause we can't talk
about indexes and seqscans without talk about indexes performance, I've to
apologize if someone thinks that it's off-topic.
Index usage has a big contention problem when running on large SMP boxes,
here are the results from running on an 8 r10000 200MHz, I've sized the
database in order to be 100% cachable by OS in order to compare memory
performance with seq_scan an index_scan, lately I've reduced
random_page_cost first to 0.5 and finally to 0.1 to force postgres to use
indexes, in both executions 1 only stream(on left in the graph) is faster
than in random_page_cost=4, but more than one stream results in high
contention rate.
These are results from tpc-h(1 first stream of 22 queries followed for s
parallel streams of same queries other order with refresh functions in
progress)
Orange shows CPU waiting for resources, what means stopped at a sem(it's odd
because all queries are read-only).
first of all is rpg=4(less time 8 streams than first(no loads)),
second=0.5(about twice the parallel than first stream) third=0.1(five times
parallel than first stream).
I've marked where the first stream ends and starts the parallel test.
