Re: Grave performance issues...

"Ztream" <ztream(at)highrad(dot)org> wrote in message news:<a0fr1a$1eil$1(at)news(dot)tht(dot)net>...
> Distribution size of 600 rows! Also, neither of the other two tables
> referenced contained more than 30 rows.
>
> *2
> Even when the involved tables are freshly vacuumed (and vacuum analyzed),
> the above query using the data amount from (*1) takes about 4 seconds to

That time does sound REALLY excessive for the quantity of data
involved.

Have you tried "EXPLAIN" to see how PostgreSQL is implementing the
query?

Are there indexes on UserID in "Data" and "GroupMember"?

Have you tried using a temporary table as an in-between stage?
Something like:

CREATE TEMPORARY TABLE stats AS SELECT ItemID, Grade,
WeightGroupID,COUNT(*)as statcount FROM Data INNER JOIN GroupMember ON
Data.UserID = GroupMember.UserID GROUP BY 1,2,3

UPDATE distribution SET value = (SELECT statcount FROM stats WHERE
ItemID = Distribution.ItemID AND Grade = Distribution.Grade AND
WeightGroupID = Distribution.WeightGroupID)

Have you tried watching the output of "vmstat 1", to see if excessive
disk I/O or swapping is the culprit?

Would you consider emailing me a pg_dump of the affected tables, so I
can try it on a computer here to see if the problem is in your SQL or
in your server?

Inquiring minds want to know ...

Calvin Dodge
Certified Linux Bigot(tm)