Message-ID: <447D61BA.1040201@superlativ.dk>
Date: Wed, 31 May 2006 11:28:26 +0200
From: Nis Jorgensen <nis@superlativ.dk>
User-Agent: Thunderbird 1.5.0.2 (Windows/20060308)
MIME-Version: 1.0
To: mario.splivalo@mobart.hr
CC: pgsql-performance@postgresql.org
Subject: Re: Speedup hint needed, if available? :)
References: <1148998681.18422.24.camel@localhost.localdomain>
In-Reply-To: <1148998681.18422.24.camel@localhost.localdomain>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mario Splivalo wrote:
> Hello again.
> 
> I have to track user subscriptions to certain mailinglists, and I also
> need to track credits users have on those mailinglists. On one side I
> have procedures that add credits, on other side I have procedures that
> subtract available credits. Add/subtract is pretty intensive, around
> 30-50 adds per minute (usualy 10 or 100 credits), and around 200-500
> subtracts per minute (usualy by one or two credits).
> 
> I have created table user_subscriptions to track user subscriptions to
> certain mailing list. I have derived subscription_id as primary key. I
> have two other tables, user_subscription_credits_given, and
> _credits_taken, wich track credits for subscription added or subtracted
> to or from certain subscription. I created those two tables so I could
> eliminate a lot of UPDATES on user_subscriptions table (if I were to
> have a column 'credits' in that table). 

It sounds to me like you have decided beforehand that the obvious
solution (update a credit field in the user_subscriptions table) is not 
going to perform well. Have you tried it? How does it perform?

If it does indeed give you performance problems, you could instead run 
some kind of batch job to update the credits field (and delete the 
/given/taken records).

Finally: You could refactor the query to get rid of the union:

SELECT u.subscription_id, u.user_id, u.mailinglist_id, u.valid_from,
u.valid_to, (
   SELECT sum(credits)
   FROM credits_given
   WHERE subscription_id = u.subscription_id
) - (
   SELECT sum(credits)
   FROM credits_taken
   WHERE subscription_id = u.subscription_id)
) AS credits
FROM user_subscriptions u
WHERE u.user_id = 1

(Not tested).

You will probably need a COALESCE around each of the subqueries to avoid
problems with nulls. <rant>The sum of an empty set of numbers is 0. The
conjunction of an empty set of booleans is true. The SQL standard 
somehow manages to get this wrong</rant>

/Nis