Hi all:
I've been running small aplications over postgres 7.2.2, they are based over a tpch-like 100Mb database (which give us about 400Mb of disk usage), system is an 8-CPU SGI Power Challenge, postgres is given 80000 shared buffers and backends 32Mb of sort-memory, so all data fits in memory and we avoid disk accesses.
When we improve seq scans, systems scales well up to 8 cpus.When we improve index scan, query performance increase but system stops scaling at about 4 proccessors, profiling shows that it is due to increased memory contention, it seems strange cause query are all read only, at the beggining of the run we insert and delete some rows, but it is only 1/10 of the time.
All of that is first of all to advise of this performance drop, and second to ask what is the difference between memory usage with seqscans and indexscans.
Thanks and regards