Re: Grave performance issues...

Dear Z,

..decided to have a bit of a play with this,

I created tables _similar_ to yours -
(guessed what data and groupmember were like):

CREATE TABLE Distribution (
Grade integer NOT NULL,
ItemID integer NOT NULL,
WeightGroupID integer NOT NULL,
Value float NOT NULL
);

CREATE TABLE Groupmember (
GroupID integer NOT NULL,
GroupName varchar(30) NOT NULL,
UserID integer NOT NULL
);

CREATE TABLE Data (
ItemID integer NOT NULL,
UserID integer NOT NULL,
weightGroupID integer NOT NULL,
Grade integer NOT NULL
);

and populated them with generated data : ( 1000, 100 and 10000 rows
respectively)

I created an index on distribution only:

CREATE INDEX dist_i1 ON distribution(Grade, ItemID, WeightGroupID);

initially the update :
UPDATE Distribution
SET Value =
(
SELECT COUNT(*)
FROM Data INNER JOIN GroupMember ON Data.UserID = GroupMember.UserID
WHERE ItemID = Distribution.ItemID
AND Grade = Distribution.Grade
AND WeightGroupID = Distribution.WeightGroupID
);

took about 60s ( slow)

Adding 3 other indexes :

CREATE INDEX groupm_i1 ON groupmember(UserID);
--
CREATE INDEX data_i1 ON data(UserID);
CREATE INDEX data_i2 ON data(Grade,ItemID,WeightGroupID);

and analyzing reduced the elapsed time for the update to 2s ( not bad )

Now I have brutalized your schema (apologies...) in the interest of
making scripted data generation easy (typed everything as numbers if
possible), but hopefully the basic idea will be appropriate...

On that note, dont use "fancy" datatypes like numeric if integer will do
(as the simple ones are faster)

On the tuning front I set postgresql.conf parameters :

shared_buffers = 16000 # 128M of shared buffers
sort_mem = 10240 # 10M of sort memory

You probably dont need the buffers that high - unless you expect the big
table(s) to have millions of rows....

(I used Pg 7.2b4 but the same ideas should work with 7.1.x)

Good luck

Mark