To: mario.splivalo@mobart.hr
cc: pgsql-performance@postgresql.org
Subject: Re: Speedup hint needed, if available? :) 
In-reply-to: <1148998681.18422.24.camel@localhost.localdomain> 
References: <1148998681.18422.24.camel@localhost.localdomain>
Comments: In-reply-to Mario Splivalo <mario.splivalo@mobart.hr>
	message dated "Tue, 30 May 2006 16:18:01 +0200"
Date: Tue, 30 May 2006 11:05:55 -0400
Message-ID: <23931.1149001555@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mario Splivalo <mario.splivalo@mobart.hr> writes:
> Here is the query which gets information on particular user, shows
> subscriptions to mailinglists and available credits on those
> mailinglists:

> SELECT u.subscription_id, u.user_id, u.mailinglist_id, u.valid_from,
> u.valid_to, sum(credits.credits_given - credits.credits_taken)::integer
> AS credits
> FROM user_subscriptions u
> LEFT JOIN 
> 	(SELECT user_subscription_credits_given.subscription_id,
> user_subscription_credits_given.credits AS credits_given, 0 AS
> credits_taken
>            FROM user_subscription_credits_given
> 	UNION ALL 
>          SELECT user_subscription_credits_taken.subscription_id, 0 AS
> credits_given, user_subscription_credits_taken.credits AS credits_taken
>            FROM user_subscription_credits_taken) credits 
> 	ON u.subscription_id = credits.subscription_id
> where
> 	u.user_id = 1
> GROUP BY u.subscription_id, u.user_id, u.mailinglist_id, u.valid_from,
> u.valid_to

Do you have realistic test data?  The EXPLAIN shows that this is pulling
275366 of the 826032 rows in the two tables, which seems like rather a
lot for a single user.  If it's reasonable that the query needs to fetch
one-third of the data, then you should resign yourself to it taking
awhile :-(

If the expected number of matching rows were much smaller, it would
make sense to use indexscans over the two big tables, but unfortunately
existing PG releases don't know how to generate an indexscan join
with a UNION ALL in between :-(.  FWIW, 8.2 will be able to do it.
In current releases the only thing I can suggest is to merge
user_subscription_credits_given and user_subscription_credits_taken
into one table so you don't need the UNION ALL.

			regards, tom lane